Solved

# Countif questions

Posted on 2012-08-27
361 Views
I want to know  If  I can use COUNTIF on a range of numbers? for example I need a count for

1 and 2 together as well as the following....

3, 4 and 5

6 thru 10

and anything over 11.

Currently I can only look up 1 number in a range... this is what I got so far.  =COUNTIF(N15:N31,Q15)

For Example I would like to count 3,4,5, together which = 15 (see below)  6 thru 10 = 1 since there isn't 1 or 2 that would = 0

6
5
5
5
5
4
4
4
4
4
4
4
4
4
4
3

0
Question by:Edward Pamias

LVL 39

Expert Comment

You can do

=COUNTIF(N15:N31,1)+COUNTIFS(N15:N31,2)
=COUNTIFS(N15:N31,">=3",N15:N31",<=5")
=COUNTIFS(N15:N31,">=6",N15:N31",<=10")

Thomas
0

LVL 14

Author Comment

=COUNTIF(N15:N31,1)+COUNTIFS(N15:N31,2)   <----this one works
The others did not work. Possible typos?
0

LVL 39

Expert Comment

I assume you're using excel 2003 or earlier? Then use these formulas instead

=COUNTIF(N15:N31,">=3")-COUNTIFS(N15:N31,">5")
=COUNTIF(N15:N31,">=6")-COUNTIFS(N15:N31,">10")

Thomas
0

LVL 14

Author Comment

No sorry, I am using excel 2010.
0

LVL 39

Accepted Solution

so then, definitely typos (placement of double quotes for the last criteria)

=COUNTIFS(N15:N31,">=3",N15:N31,"<=5")
=COUNTIFS(N15:N31,">=6",N15:N31,"<=10")

Thomas
0

LVL 14

Author Closing Comment

Thank you!
0

LVL 39

Expert Comment

Thomas
0

## Featured Post

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.