EXCEL multiple criteria in countif function
 

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

EXCEL multiple criteria in countif function
 

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

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

--
Message posted from http://www.ExcelForum.com

EXCEL multiple criteria in countif function
 

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)

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

EXCEL multiple criteria in countif function
 

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)

"shades " wrote in message
...
For two or more criteria, you can use SUMPRODUCT instead of COUNTIF

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


---
Message posted from http://www.ExcelForum.com/

EXCEL multiple criteria in countif function
 

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

(COUNTIF(OR(condition1,condition2))


HTH

Chris

EXCEL multiple criteria in countif function
 

"ChrisJForeman" wrote...
I thnk what you need is the OR function nested within the COUNTIF function:

(COUNTIF(OR(condition1,condition2))

Won't work. Try it.

--
To top-post is human, to bottom-post and snip is sublime.

EXCEL multiple criteria in countif function
 

"Bob Phillips" wrote...
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:A10 0))))

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?

--
To top-post is human, to bottom-post and snip is sublime.

EXCEL multiple criteria in countif function
 

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" wrote in message
...
"Bob Phillips" wrote...
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:A10 0))))

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?

--
To top-post is human, to bottom-post and snip is sublime.

EXCEL multiple criteria in countif function
 

"Bob Phillips" wrote...
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.

EXCEL multiple criteria in countif function
 

"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
Harlan,

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.

EXCEL multiple criteria in countif function
 

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

--
Message posted from http://www.ExcelForum.com