EXCEL multiple criteria in countif function

[Robert]

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

 

[shades]

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

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

 

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

 

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

 

[ChrisJForeman]

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

(COUNTIF(OR(condition1,condition2))


HTH

Chris

 

[Harlan Grove]

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

(COUNTIF(OR(condition1,condition2))

Won't work. Try it.

 

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

...

If the last formula works, so would

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

no?

 

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

 

[Harlan Grove]

Harlan,

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

....

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.

 

[Bob Phillips]

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

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

 

[Natalie]

Thankyou! I knew there had to be a way to do this but I just couldn'
figure it out..