SUMPRODUCT- double unary vs. *

[Epinn]

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

 

[Epinn]

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


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

 

[Epinn]

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


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


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