Is anything wrong with this formula?
 

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

EggHeadCafe - Software Developer Portal of Choice
..NET - Generic Sorting Using Reflection
http://www.eggheadcafe.com/tutorials...orting-us.aspx

Is anything wrong with this formula?
 

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


<Kelly Johns wrote in message
...
=SUMPRODUCT('Cp Monitoring'!P2:P100="review")*(AE2:AE100="yes")

EggHeadCafe - Software Developer Portal of Choice
.NET - Generic Sorting Using Reflection
http://www.eggheadcafe.com/tutorials...orting-us.aspx

Is anything wrong with this formula?
 

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'!P2:P100="review"),--(AE2:AE100="yes"))



--
If this helps, please remember to click yes.


"Kelly Johns" wrote:

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

EggHeadCafe - Software Developer Portal of Choice
..NET - Generic Sorting Using Reflection
http://www.eggheadcafe.com/tutorials...orting-us.aspx
.

Is anything wrong with this formula?
 

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

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

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------


"Kelly Johns" wrote:

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

EggHeadCafe - Software Developer Portal of Choice
..NET - Generic Sorting Using Reflection
http://www.eggheadcafe.com/tutorials...orting-us.aspx
.

Is anything wrong with this formula?
 

"Paul C" wrote:
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 -----

"Paul C" wrote in message
...
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'!P2:P100="review"),--(AE2:AE100="yes"))



--
If this helps, please remember to click yes.


"Kelly Johns" wrote:

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

EggHeadCafe - Software Developer Portal of Choice
..NET - Generic Sorting Using Reflection
http://www.eggheadcafe.com/tutorials...orting-us.aspx
.

Is this formula wrong
 

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 SUMPRODUCT
function.
You probably intended to say
=SUMPRODUCT(('Cp Monitoring'!P2:P100="review")*(AE2:AE100="yes")) ?
--
David Biddulph


<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/tutorials...ework-tec.aspx

Is this formula wrong
 

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 SUMPRODUCT
function.
You probably intended to say
=SUMPRODUCT(('Cp Monitoring'!P2:P100="review")*(AE2:AE100="yes")) ?
--
David Biddulph


<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/tutorials...ader-data.aspx

Is this formula wrong
 

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?
--
David Biddulph

<Kelly Johns wrote in message
...
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 SUMPRODUCT
function.
You probably intended to say
=SUMPRODUCT(('Cp Monitoring'!P2:P100="review")*(AE2:AE100="yes")) ?
--
David Biddulph


<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/tutorials...ework-tec.aspx

Is this formula wrong
 

<Kelly Johns wrote:
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'!P2:P100="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 -----

<Kelly Johns wrote in message
...
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 SUMPRODUCT
function.
You probably intended to say
=SUMPRODUCT(('Cp Monitoring'!P2:P100="review")*(AE2:AE100="yes")) ?
--
David Biddulph


<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/tutorials...ader-data.aspx

Is anything wrong with this formula?
 

PS....

I wrote:
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 P1:P100 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 -----

"Joe User" <joeu2004 wrote in message
...
"Paul C" wrote:
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 -----

"Paul C" wrote in message
...
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'!P2:P100="review"),--(AE2:AE100="yes"))



--
If this helps, please remember to click yes.


"Kelly Johns" wrote:

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

EggHeadCafe - Software Developer Portal of Choice
..NET - Generic Sorting Using Reflection
http://www.eggheadcafe.com/tutorials...orting-us.aspx
.

Intention
 

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 P1:P100 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 -----

"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/tutorials...reate-and.aspx

Intention
 

<Kelly Johns wrote:
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'!P2:P100="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 -----

<Kelly Johns wrote in 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 P1:P100 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 -----

"Joe User" <joeu2004 wrote in message
...
"Paul C" wrote:
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 -----

"Paul C" wrote in message
...
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'!P2:P100="review"),--(AE2:AE100="yes"))



--
If this helps, please remember to click yes.


"Kelly Johns" wrote:

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