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