COUNTIF quandry

[Guest]

I've got this:
=COUNTIF(SWABS!F7:F237,"=*Station 1*")

How can I add =COUNTIF(SWABS!L7:L237,"=*F*")

to this to make it all work? In other words I want to count the number of
"Station 1" values and the number of "F" corresponding values.

THANKS!

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(FIND("Station
1",SWABS!F7:F237))),--(ISNUMBER(FIND("F",,SWABS!L7:L237))))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Harlan Grove]

=SUMPRODUCT(--(ISNUMBER(FIND("Station 1",SWABS!F7:F237))),
--(ISNUMBER(FIND("F",,SWABS!L7:L237))))

....

Note the ,, typo.

Or use just one ISNUMBER call.

=SUMPRODUCT(--ISNUMBER(FIND("Station 1",SWABS!F7:F237)
+FIND("F",SWABS!L7:L237)))

or, if the OP could suffer an array formula,

=COUNT(FIND("Station 1",SWABS!F7:F237)+FIND("F",SWABS!L7:L237))

No magic in using +. - * / would work equally well. ^ might overflow.

 

[Guest]

Thanks, Bob! That's not returning what's expected. It's returning "0" when
there's actually 2 "F's".

In other words for all of the "Station 1" entries (8 total) there are only 2
that have "F's". I'm not sure what needs changed...?

 

[KL]

I've got this:
=COUNTIF(SWABS!F7:F237,"=*Station 1*")

How can I add =COUNTIF(SWABS!L7:L237,"=*F*")

to this to make it all work? In other words I want to count the number of
"Station 1" values and the number of "F" corresponding values.

THANKS!

If you need to count either those that include "Station 1" or those that
include "F", then:

=SUMPRODUCT(COUNTIF(SWABS!F7:F237,{"*Station 1*","*F*"}))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: http://mvp.support.microsoft.com/profile/Kirill

 

[Guest]

Thanks very much, Harlan!

--
www.Marzetti.com

....

Note the ,, typo.

Or use just one ISNUMBER call.

=SUMPRODUCT(--ISNUMBER(FIND("Station 1",SWABS!F7:F237)
+FIND("F",SWABS!L7:L237)))

or, if the OP could suffer an array formula,

=COUNT(FIND("Station 1",SWABS!F7:F237)+FIND("F",SWABS!L7:L237))

No magic in using +. - * / would work equally well. ^ might overflow.

 

[KL]

Upps! I guess I misunderstood the question...

If you need to count either those that include "Station 1" or those that
include "F", then:

=SUMPRODUCT(COUNTIF(SWABS!F7:F237,{"*Station 1*","*F*"}))

KL