conditional formula using sumproduct

[ferde]

A--------------B-
6N FALSE
6N FALSE
7N TRUE
8N TRUE
6N TRUE

I am using this as an array formula in C2 but getting zero for an answer
when the correct answer would be 1. Can anyone tell me what I am doing
wrong. I am looking for the formula to count the number of times "TRUE" is
found in B2:B6 IF "6N" is also found in A2:A6. Any help would be greatly
appreciated.

SUMPRODUCT((A2:A6="6N")*(B2:B6="TRUE"))

correct answer =1

 

[Mike H]

Hi,

Remove the quotes from TRUE and no need to array enter

=SUMPRODUCT((A2:A6="6N")*(B2:B6=TRUE))

Mike

 

[ferde]

I knew I was getting close to solving this ....Thank you so much Mike.
...works great!

 

[T. Valko]

If column B contains only the logical values TRUE or FALSE:

=SUMPRODUCT((A2:A6="6N")*B2:B6)

 

[Earl Kiosterud]

Ferde,

I dispair that Excel is so anal about data typing. If you have TRUE in a cell, and it's
centered, (presuming you haven't set left, center, or right alignment), then you know that
Excel has typed it as boolean. Your formula won't work because the quotes around "TRUE"
means it's text data type, not boolean. If the TRUE in your cell had been text (such as
with a preceding apostrophe, or having first been formatted for text (before TRUE was
entered into the cell), then your formula would have worked. It's just one more gotcha.

Similar problems occur when Excel has typed a cell as text, but it "looks" like a number.
Happens frequently when numeric data has been pasted in, such as from a web page or other
program. In that case, some formulas will pick up the numbers, and some won't.

The nerds at Excel think users understand data typing, so they designed it with a
programmer's approach, not with a typical end-user's perspective.

 

[ferde]

Thank you works great

If column B contains only the logical values TRUE or FALSE:

=SUMPRODUCT((A2:A6="6N")*B2:B6)

 

[ferde]

Thank you very much for your reply

I guess that your TRUE and FALSE are not text strings but Boolean values.
Try deleting the quote marks around TRUE.

In fact as a Boolean you can delete the =TRUE, so the formula simplifies to
=SUMPRODUCT((A2:A6="6N")*(B2:B6))

 

[ferde]

I appreciate the explanation that will help me in the future from making
similiar mistakes.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Daniel.C]

If values in column B are boolean values and not text values, you have to
use
SUMPRODUCT((A2:A6="6N")*(B2:B6=TRUE))