Is anything wrong with this formula?

[Kelly Johns]

=SUMPRODUCT('Cp Monitoring'!P2100="review")*(AE2:AE100="yes")

EggHeadCafe - Software Developer Portal of Choice
..NET - Generic Sorting Using Reflection
http://www.eggheadcafe.com/tutorial...add-6ef18afe0134/net--generic-sorting-us.aspx

 

[David Biddulph]

Yes, it is wrong.
Look again at the parentheses surrounding the arguments of the SUMPRODUCT
function.
You probably intended to say
=SUMPRODUCT(('Cp Monitoring'!P2100="review")*(AE2:AE100="yes")) ?

 

[Paul C]

It appears to be missing a set of (). Also the -- operator appears to help
with conditional arrays in that it forces evaluation

Try =SUMPRODUCT(--('Cp Monitoring'!P2100="review"),--(AE2:AE100="yes"))

 

[Ms-Exl-Learner]

I hope that your formula should be like this...

=SUMPRODUCT(('Cp Monitoring'!P2100="review")*('Cp
Monitoring'!AE2:AE100="yes"))

If this post helps, Click Yes!

 

[Joe User]

It appears to be missing a set of ().
Right.


Also the -- operator appears to help
with conditional arrays in that it forces evaluation

Misdirection. Either form should work equally well.

The issue is.... Conditional expressions return an array of TRUE and FALSE
values. SUMPRODUCT would treat all such values as zero. But when the
boolean array is involved in an arithmetic operation, TRUE and FALSE are
treated as 1 and 0 respectively, as intended. Thus, SUMPRODUCT sees an
array of numerical values instead of boolean values. Any arithmetic
operation will do that. "--" (double negation) is one arithmetic operation;
"*" (multiplication) is simply another arithmetic operation.


----- originally message -----

 

[Kelly Johns]

Thank-you very much for your help. I am still not getting the data required, I am getting a '0' as a response. But you were correct as to why the formula was wrong. Headache.



David Biddulph wrote:

Yes, it is wrong.
24-Nov-09

Yes, it is wrong
Look again at the parentheses surrounding the arguments of the SUMPRODUC
function
You probably intended to sa
=SUMPRODUCT(('Cp Monitoring'!P2100="review")*(AE2:AE100="yes"))
-
David Biddulp

<Kelly Johns> wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Top 10 .NET Framework Technologies to Learn in 2007
http://www.eggheadcafe.com/tutorial...44-469c1766614c/top-10-net-framework-tec.aspx

 

[Kelly Johns]

Thank-you very much for your help. I am still not getting the data required, I am getting a '0' as a response. But you were correct as to why the formula was wrong. Headache.



David Biddulph wrote:

Yes, it is wrong.
24-Nov-09

Yes, it is wrong
Look again at the parentheses surrounding the arguments of the SUMPRODUC
function
You probably intended to sa
=SUMPRODUCT(('Cp Monitoring'!P2100="review")*(AE2:AE100="yes"))
-
David Biddulp

<Kelly Johns> wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Generic GetXmlReader Data Access Method
http://www.eggheadcafe.com/tutorial...d-b6ebb3575e5e/generic-getxmlreader-data.aspx

 

[David Biddulph]

Are you sure that "review" and "yes" are the only things in columns P and AE
in the relevant rows? No spaces?
Do =LEN('Cp Monitoring'!P2) and =LEN(AE2) [or the same in the relevant rows]
return 6 and 3 respectively?

 

[Joe User]

Thank-you very much for your help. I am still
not getting the data required, I am getting a
'0' as a response.

Well, you neglect to tell what you intend to accomplish with the SUMPRODUCT
expression.

Wild-ass guess: perhaps you intended to write:

=SUMPRODUCT(('Cp Monitoring'!P2100="review")
*('Cp Monitoring'!AE2:AE100="yes"))

That counts the number instances of "review" in one range __and__ "yes" in
the other range in the __same__ worksheet, not necessarily the current
worksheet.


----- original message -----

 

[Joe User]

PS....

Any arithmetic operation will do that.

Of course, you have to choose the correct arithmetic operation for the
intended logic. But Kelly neglects to tell us what the intended logic is,
and what was wrong in the first place. (The missing pair of parentheses
might have been just a posting error, not a real error in the worksheet.)

If the intent is to count the number of instances of "review" in one range
__and__ "yes" in the other range, then either "*" or "--(...),--(...)" will
do.

But if the intent is to count the number of instances of "review" in one
range __or__ "yes" in the other range, then Kelly should use "+" instead
"*".

But perhaps Kelly did not intend to __count__ at all. Perhaps the intent is
to add some other range altogether, based on some combination of those
conditions, "and" or "or" we don't know. The error might be a missing third
parameter.

And/or perhaps Kelly intended to compare two ranges in the __same__
worksheet, not one range in one named worksheet and the other range in the
current worksheet, which is not necessarily the named worksheet.

Finally (but not exhausting all other possible errors), perhaps the real
error is a mismatch in the size of the ranges. That is, if the Kelly did
not copy-and-paste the formula, as evidenced by the typo in the posting,
perhaps Kelly also mistyped the actual ranges in the worksheet. For all we
know, they might be P1100 and AE2:A100 [sic] -- note the subtle
difference.

Since Kelly neglected to say exactly what is wrong and most of us lack the
gift of clairvoyance, we really do not have enough information to help
Kelly, other than by dumb luck (aka a wild-ass guess).


----- original message -----

 

[Kelly Johns]

My intention was to find out how many review conferences were on time. I.e review or initial, hence the review part, and for on time the yes or no, hence the yes part.

I wanted the formula to count which were reviews and then which were on time.



Joe User wrote:

PS....
24-Nov-09

PS...

I wrote

Of course, you have to choose the correct arithmetic operation for th
intended logic. But Kelly neglects to tell us what the intended logic is
and what was wrong in the first place. (The missing pair of parenthese
might have been just a posting error, not a real error in the worksheet.

If the intent is to count the number of instances of "review" in one rang
__and__ "yes" in the other range, then either "*" or "--(...),--(...)" wil
do

But if the intent is to count the number of instances of "review" in on
range __or__ "yes" in the other range, then Kelly should use "+" instea
"*"

But perhaps Kelly did not intend to __count__ at all. Perhaps the intent i
to add some other range altogether, based on some combination of thos
conditions, "and" or "or" we do not know. The error might be a missing thir
parameter

And/or perhaps Kelly intended to compare two ranges in the __same_
worksheet, not one range in one named worksheet and the other range in th
current worksheet, which is not necessarily the named worksheet

Finally (but not exhausting all other possible errors), perhaps the rea
error is a mismatch in the size of the ranges. That is, if the Kelly di
not copy-and-paste the formula, as evidenced by the typo in the posting
perhaps Kelly also mistyped the actual ranges in the worksheet. For all w
know, they might be P1100 and AE2:A100 [sic] -- note the subtl
difference

Since Kelly neglected to say exactly what is wrong and most of us lack th
gift of clairvoyance, we really do not have enough information to hel
Kelly, other than by dumb luck (aka a wild-ass guess)

----- original message ----

"Joe User" <joeu2004> wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Excel Macros - Create And Run in C# at Runtime
http://www.eggheadcafe.com/tutorial...fc-5ef649a032b9/excel-macros--create-and.aspx

 

[JoeU2004]

My intention was to find out how many
review conferences were on time. I.e
review or initial, hence the review
part, and for on time the yes or no,
hence the yes part.

So, as I wrote in another response in this thread....

Wild-ass guess: perhaps you intended to write:

=SUMPRODUCT(('Cp Monitoring'!P2100="review")
*('Cp Monitoring'!AE2:AE100="yes"))

That counts the number instances of "review" in one range __and__ "yes" in
the other range in the __same__ worksheet, not necessarily the current
worksheet.


----- original message -----

My intention was to find out how many review conferences were on time. I.e
review or initial, hence the review part, and for on time the yes or no,
hence the yes part.

I wanted the formula to count which were reviews and then which were on
time.



Joe User wrote:

PS....
24-Nov-09

PS....

I wrote:

Of course, you have to choose the correct arithmetic operation for the
intended logic. But Kelly neglects to tell us what the intended logic is,
and what was wrong in the first place. (The missing pair of parentheses
might have been just a posting error, not a real error in the worksheet.)

If the intent is to count the number of instances of "review" in one range
__and__ "yes" in the other range, then either "*" or "--(...),--(...)"
will
do.

But if the intent is to count the number of instances of "review" in one
range __or__ "yes" in the other range, then Kelly should use "+" instead
"*".

But perhaps Kelly did not intend to __count__ at all. Perhaps the intent
is
to add some other range altogether, based on some combination of those
conditions, "and" or "or" we do not know. The error might be a missing
third
parameter.

And/or perhaps Kelly intended to compare two ranges in the __same__
worksheet, not one range in one named worksheet and the other range in the
current worksheet, which is not necessarily the named worksheet.

Finally (but not exhausting all other possible errors), perhaps the real
error is a mismatch in the size of the ranges. That is, if the Kelly did
not copy-and-paste the formula, as evidenced by the typo in the posting,
perhaps Kelly also mistyped the actual ranges in the worksheet. For all
we
know, they might be P1100 and AE2:A100 [sic] -- note the subtle
difference.

Since Kelly neglected to say exactly what is wrong and most of us lack the
gift of clairvoyance, we really do not have enough information to help
Kelly, other than by dumb luck (aka a wild-ass guess).

----- original message -----