SUMPRODUCT- double unary vs. *
I did try to insert double unary before the argument checking for "AA" but I still got the error. <<
This is because I didn't change the * to , (comma).
Thanks to Roger, the following formula works with null.
=SUMPRODUCT(--(A2:A6="AA"),C2:C6)
This brings back Ken's point of * doesn't work and , works.
The more I play with -- , * the more confused I am and don't know when to use which. It is trial an error.
Still don't understand why these two
=SUMPRODUCT(--(A2:A6="AA"),--(C2:C6))
=SUMPRODUCT((A2:A6="AA")*(C2:C6))
won't work with null and why I have to write the formula this way:
=SUMPRODUCT(--(A2:A6="AA"),C2:C6)
Epinn
"Epinn" wrote in message ...
One set of TRUE, FALSE ..... gives an error while multiplying two sets of TRUE, FALSE solves the problem. <<
So, is coercing in play here and it solves the problem? I am not convinced. I did try to insert double unary before the argument checking for "AA" but I still got the error. I had to check for null.
Epinn
"Epinn" wrote in message ...
This is continuation from another thread.
Following is an excerpt from Ken's post.
************************************************** ************************************************** ********
With the following data in A1:B5
abc def
1 2
3 4
2 3
4 5
Try each of the following formulas:-
=SUMPRODUCT(A1:A5*B1:B5)
=SUMPRODUCT(A1:A5,B1:B5)
First one fails, second one doesn't.
************************************************** ************************************************** *********
My new post:
Ken's above example has both text and numbers in the data set and at least double unary/comma works ......
I also have text and numbers but this time neither double unary/comma nor * works.
=SUMPRODUCT(--(A2:A6="AA"),--(C2:C6))
=SUMPRODUCT((A2:A6="AA")*(C2:C6))
Both returned #VALUE!.
If I remove the text from the data set, both formulae work fine.
I fix the error with the following formula:-
=SUMPRODUCT((A2:A6="AA")*(C2:C6<""),C2:C6)
To my surprise, checking for <"" takes care of *all text* and not just null. The formula seems to do well regardless of whether the text in column C corresponds to AA in column A or not.
I did evaluate formula, but couldn't explain why
=SUMPRODUCT((A2:A6="AA")*(C2:C6)) won't work and
=SUMPRODUCT((A2:A6="AA")*(C2:C6<""),C2:C6) works.
One set of TRUE, FALSE ..... gives an error while multiplying two sets of TRUE, FALSE solves the problem.
I am missing something here. Please explain.
Epinn
|