COUNTIF quandry

J

JohnLute

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!
 
B

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)
 
H

Harlan Grove

Bob Phillips said:
=SUMPRODUCT(--(ISNUMBER(FIND("Station 1",SWABS!F7:F237))),
--(ISNUMBER(FIND("F",,SWABS!L7:L237))))
Click to expand...
....

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.
 
J

JohnLute

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...?
 
K

KL

JohnLute said:
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!
Click to expand...

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
 
J

JohnLute

Thanks very much, Harlan!

--
www.Marzetti.com


Harlan Grove said:
....

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.
Click to expand...
 
K

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*"}))
Click to expand...

KL
 
Top