Countif or Sumproduct
 

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

Countif or Sumproduct
 

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


"Harley" wrote:

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

Countif or Sumproduct
 

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
--
Kind regards,

Niek Otten



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

Countif or Sumproduct
 

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!

In article ,
Harley wrote:

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

Countif or Sumproduct
 

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

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

XL2002, WinXP-Pro


"Domenic" wrote:

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!

In article ,
Harley wrote:

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

Countif or Sumproduct
 

I know that feeling... :)

In article ,
"Ron Coderre" wrote:

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

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

XL2002, WinXP-Pro


"Domenic" wrote:

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!

In article ,
Harley wrote:

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

Countif or Sumproduct
 

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

"Domenic" wrote:

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!

In article ,
Harley wrote:

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

Countif or Sumproduct
 

Try...

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

Hope this helps!

In article ,
Harley wrote:

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

"Domenic" wrote:

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!

In article ,
Harley wrote:

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

Countif or Sumproduct
 

That got it!!

Thanks!

"Domenic" wrote:

Try...

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

Hope this helps!

In article ,
Harley wrote:

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

"Domenic" wrote:

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!

In article ,
Harley wrote:

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