Solved

COUNTIFS Function not adding multiple results

Posted on 2013-01-07
7
437 Views
Last Modified: 2013-01-07
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
0
Comment
Question by:MCaliebe
7 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 38751207
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
 

Author Comment

by:MCaliebe
ID: 38751227
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
 
LVL 10

Accepted Solution

by:
etech0 earned 150 total points
ID: 38751235
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 13

Expert Comment

by:Shanan212
ID: 38751237
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
 

Author Closing Comment

by:MCaliebe
ID: 38751247
Great explanation.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38751253
Glad to help!
0
 
LVL 24

Expert Comment

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

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OCT or Config.xml 2 31
Excel Formula 4 28
Auto populate in Cascade dropdown 3 24
macro for closing opened workbook 6 18
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now