Sumproduct returning "0"

[meileetan]

Hello,
I am using Excel 2007.
In cells A1: A3, I have the values A, B, C.
In cells B1:B3, I have the values 1, 2, 1.

I want to count the number of cells in column A where its corresponding value in column B=1. The expected answer should be 2.

I tried 2 formulas:
1. =sumproduct(--(A1:A3),(B1:B3=1))
Result = #VALUE!

2. =sumproduct(A1:A3,B1:B3=1)
Result = 0

Can you please tell me where I've gone wrong?

Melissa

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(--(A1:A3="B"),(B1:B3=1))

which returns zero because you only have one B and its corresponding value is 2 not 1. To count the number of cells in column B that contain 1:

=SUMPRODUCT(--(B1:B3=1))

or:

=COUNTIF(B1:B3,1)

both of which return 2.

 

[JackDanIce]

Try:

=SUMPRODUCT(--NOT(ISBLANK(A1:A3)),--(B1:B3=1))

 

[hiker95]

meileetan,

Try:
=SUMPRODUCT(--(B1:B3=1),--(A1:A3<>""))

 

[meileetan]

Thanks Andrew. Indeed the countif formula is what I need.
However, could you also explain why my sumproduct formula is behaving as I had described? Just trying to make sense of sumproduct.

 

[JackDanIce]

Your formula:

=sumproduct(--(A1:A3),(B1:B3=1))

Where I have highlighted red, is not returning a TRUE or FALSE argument, for the double -- to convert into a number that can be multiplied by evaluating if (B1:B3=1), which also is not being converted into a number because now you're missing the double --

The formula Andrew provided:

=SUMPRODUCT(--(A1:A3="B"),(B1:B3=1))

And the one I provided

=SUMPRODUCT(--NOT(ISBLANK(A1:A3)),--(B1:B3=1))

Both provide TRUE or FALSE conditions for both arguments, which you then evaluate with double unary to convert into a 1 or 0, i.e. numeric values which can then be used within the SUMPRODUCT to provide a numeric answer

 

[Andrew Poulsom]

My formula should be:

=SUMPRODUCT(--(A1:A3="B"),--(B1:B3=1))