Link to home
Start Free TrialLog in
Avatar of MCaliebe
MCaliebeFlag for United States of America

asked on

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
Avatar of etech0
etech0
Flag of United States of America image

Countifs will count all records that fulfill BOTH conditions. To achieve what you need, try this:

=COUNTIF(J6:AM6,"I*")+COUNTIF(J6:AM6,"P*")
Avatar of MCaliebe

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of etech0
etech0
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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

then you can put multiple criteria in between the {}.