Avatar of MCaliebe
MCaliebe
Flag 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
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Steve

8/22/2022 - Mon
etech0

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

=COUNTIF(J6:AM6,"I*")+COUNTIF(J6:AM6,"P*")
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
etech0

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shanan212

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
MCaliebe

ASKER
Great explanation.
etech0

Glad to help!
Steve

you can use SUM(COUNTIF(J6:AM6,{"I*","P*"})) entered with [ctrl]+[shift]+[enter]

then you can put multiple criteria in between the {}.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.