COUNTIFS Function not adding multiple results

I am trying to use the COUNTIFS to state multiple criteria within a range to count.  Can someone look at the attached example and tell me what might be wrong with my formula>  I assume that

=COUNTIFS(J6:AM6,"I*", J6:AM6,"P*")
is going to count all cells in a range begining with I or P.

Additionally, can the Count functions be used to determine what does not meet a criteria such as... count everything in range J6:AM6 except "I*" and "P*"

Thanks,

MC
MCaliebeAsked:
Who is Participating?
 
etech0Commented:
For this one, you'd probably need to use countifs.

Try this:

=COUNTIFS(J6:AM6,"<>I*", J6:AM6,"<>P*")

What it's doing is counting all the records where it doesn't start with I and it doesn't start with P. The rule is that Countifs uses AND, so if you want OR, you need to add (like above).
0
 
etech0Commented:
Countifs will count all records that fulfill BOTH conditions. To achieve what you need, try this:

=COUNTIF(J6:AM6,"I*")+COUNTIF(J6:AM6,"P*")
0
 
MCaliebeAuthor Commented:
Thank you.  The help file is missing that statement.

Can you look at my second question..I edited it most likely as you were answering.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Shanan212Commented:
To count all that begins with i* or p*
=COUNTIF(J6:AM6,"i*")+COUNTIF(J6:AM6,"P*")

Open in new window


To count all that does not begin with i* or p*
=COUNTIFS(J6:AM6,"<>i*",J6:AM6,"<>P*")

Open in new window

0
 
MCaliebeAuthor Commented:
Great explanation.
0
 
etech0Commented:
Glad to help!
0
 
SteveCommented:
you can use SUM(COUNTIF(J6:AM6,{"I*","P*"})) entered with [ctrl]+[shift]+[enter]

then you can put multiple criteria in between the {}.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.