Countif or Sumproduct

H

Harley

I need to count all cells in one column containing "FL" anywhere in the
sentence if the condition in another column is "Y"
 
R

Ron Coderre

With:
A1:A10 containing the FL cells
B1:B10 containing the Y cells

Try this:
D1: FL
D2: Y
D3:
=SUMPRODUCT(--((LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),UPPER(D1),"")))<>LEN(A1:A10))*(B1:B10=D2))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
N

Niek Otten

I would introduce 2 helper columns. Of course it can be done many other
ways, but this way it is easy to check that your formula does what it is
supposed to do.
If your "Y"s are in A and your text in B, Then this in C:

=IF(AND(A1="y",NOT(ISERROR(C1))),1,0)

and this in D:

=IF(AND(A1="y",NOT(ISERROR(C1))),1,0)

both copied down as far as needed

Sum column D
 
D

Domenic

Try something like the following...

=SUMPRODUCT(--(ISNUMBER(FIND("FL",A1:A10))),--(B1:B10="Y"))

Note that FIND is case sensitive. If you don't want the case to be
sensitive, use SEARCH instead.

Hope this helps!
 
R

Ron Coderre

THAT's what I was trying to think of!
It completely eluded me.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
H

Harley

This did work - Thanks. I do have one situation where I need the formula to
find "FLH" or "FLT" under the same scenrio. Any thoughts?
Thanks again,

Harley
 
D

Domenic

Try...

=SUMPRODUCT((ISNUMBER(FIND({"FLH","FLT"},A1:A10)))*(B1:B10="Y"))

Hope this helps!
 
Top