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

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*"))

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

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/

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

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.

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

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

(COUNTIF(OR(condition1,condition2))


HTH

Chris

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