Is anything wrong with this formula?
kellyjohns
EggHeadCafe - Software Developer Portal of Choice
.NET - Generic Sorting Using Reflection
http://www.eggheadcafe.com/tutorials/aspnet/73dbcd68-bcb5-4531-9add-6ef18afe0134/net--generic-sorting-us.aspx
David Biddulph
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
news:200911248358k...@hotmail.com...
Paul C
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/aspnet/73dbcd68-bcb5-4531-9add-6ef18afe0134/net--generic-sorting-us.aspx
> .
>
Ms-Exl-Learner
=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/aspnet/73dbcd68-bcb5-4531-9add-6ef18afe0134/net--generic-sorting-us.aspx
> .
>
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 -----
"Paul C" <Pa...@discussions.microsoft.com> wrote in message
news:8A59FD84-BA1F-49C3...@microsoft.com...
kellyjohns
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/aspnet/4b9d6e1c-abe2-4f4d-9abd-b6ebb3575e5e/generic-getxmlreader-data.aspx
kellyjohns
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/aspnet/7382ebfc-259d-4590-8344-469c1766614c/top-10-net-framework-tec.aspx
David Biddulph
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
news:200911249351k...@hotmail.com...
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'!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
news:200911249352k...@hotmail.com...
Joe User
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
news:e$fnXBRbK...@TK2MSFTNGP06.phx.gbl...
kellyjohns
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/aspnet/fc339e7c-ce0f-4a1e-96fc-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'!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
news:20091124114541...@hotmail.com...