Please help me with a Conditional Count...
 

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 "*" or a "Fw:*" in Column At?

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

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

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

Thanks!

Please help me with a Conditional Count...
 

Try:

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


"SisterDell" wrote:

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 "*" or a "Fw:*" in Column At?

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

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

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

Thanks!

Please help me with a Conditional Count...
 

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

"Toppers" wrote:

Try:

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


"SisterDell" wrote:

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 "*" or a "Fw:*" in Column At?

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

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

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

Thanks!

Please help me with a Conditional Count...
 

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

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

HTH

"SisterDell" wrote:

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

"Toppers" wrote:

Try:

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


"SisterDell" wrote:

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 "*" or a "Fw:*" in Column At?

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

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

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

Thanks!

Please help me with a Conditional Count...
 

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

"Toppers" wrote:

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

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

HTH

"SisterDell" wrote:

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

"Toppers" wrote:

Try:

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


"SisterDell" wrote:

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 "*" or a "Fw:*" in Column At?

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

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

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

Thanks!