EXCEL multiple criteria in countif function

R

Robert

is it possible to have two criteria for a countif
statement like (COUNTIF(A1:A100,=2))or(COUNTIF
(A1:A100,"*2*"))
 
S

shades

For two or more criteria, you can use SUMPRODUCT instead of COUNTIF

=SUMPRODUCT((A1:A1000=2)*(A1:A100,"*2*")
 
B

Bob Phillips

Hi Robert,

This should be all you need

=SUMPRODUCT(--(ISNUMBER(FIND("2",A1:A100))))
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

That won't work as the syntax is wrong ("*2*"), and it is an AND not OR
test. What is closest is

=SUMPRODUCT((A1:A100=2)+(ISNUMBER(FIND("2",A1:A100))))

but this counts a single 2 twice, so all you need is

=SUMPRODUCT(--(ISNUMBER(FIND("2",A1:A100))))

as I gave previously.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
C

ChrisJForeman

I thnk what you need is the OR function nested within the COUNTIF function:

(COUNTIF(OR(condition1,condition2))


HTH

Chris
 
H

Harlan Grove

That won't work as the syntax is wrong ("*2*"), and it is an AND not OR
test. What is closest is

=SUMPRODUCT((A1:A100=2)+(ISNUMBER(FIND("2",A1:A100))))

but this counts a single 2 twice, so all you need is

=SUMPRODUCT(--(ISNUMBER(FIND("2",A1:A100))))

as I gave previously.
Click to expand...
...

If the last formula works, so would

=COUNTIF(A1:A100,"*2*")

no?
 
B

Bob Phillips

Harlan,

Seemingly yes, but it doesn't catch a singleton 2.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

Harlan Grove

Bob Phillips said:
Harlan,

Seemingly yes, but it doesn't catch a singleton 2.
Click to expand...
....

Actually it'd catch "2", but it only works with cells containing text. It
fails on any numbers. So another alternative

=SUMPRODUCT(--(SUBSTITUTE(A1:A100,"2","")=A1:A100&""))

which is unlikely to be as efficient as your formula, but only uses one
level of function call nesting.
 
B

Bob Phillips

Harlan Grove said:
Actually it'd catch "2", but it only works with cells containing text. It
fails on any numbers.
Click to expand...

Yes, that's right, I did say a singleton 2, not "2", whereas it actually
misses any numeric value with 2 in it.
 
Top