Please help me with a Conditional Count...

[Guest]

Frustrated Newbie...
What is wrong with the following formula I've created, if I'm trying to
count the number of occurrences in Column B that have 1's next to them AND
have either a "Re:*" or a "Fw:*" in Column At?

A B
FYI 1
Re: FYI 1
Re: FYI
FW: Re: FYI 1
Hi 1
FW: Hi 1

Formula:
=SUMPRODUCT((A1:A6="Re:*")*(A1:A6="Fw*")*(B1:B6=1))

I would expect to get back a value of 3. I'm getting 0 and no errors.

Thanks!

 

[Guest]

Try:

=SUMPRODUCT(--(LEFT(A1:A6,3)={"re:","fw:"})*(B1:B6=1))

 

[Guest]

Thank you SO much. It worked! (If you get a chance, could you possibly
explain why it worked?)

 

[Guest]

SUMPRODUCT doesn't allow wildcards for I put in check to match the 3 leftmost
(LEFT function) characters in column A. The {"re:","fw:"} is an OR condition.

In your original formual, apart from the wildcard problem, the
(A1:A6="Re:*")*(A1:A6="Fw*") is an AND condition so you would have returned 0
anyway.

HTH

 

[Guest]

I now understand. Thank you. Any chance you teach a course on Excel?