The CountIf function is perfect for counting the number of times an instance of something occurs in a range.
The syntax for the CountIf function is:
=Countif(range, criteria)
How do we use this?
1. In a blank cell type =CountIf(
2. Click and drag the range to look in (this automatically fills your formula)
3. Type, (comma)
4. Enter the criteria and hit enter.
For example:
To count numbers in a range greater than 100 your criteria is “>100″ or
=CountIf(A1:A10,”>100″)
To count the total times a word appears in a range your criteria is “yourwordhere” or
=CountIf(A1:A10,”yourwordhere”)
July 23rd, 2005 at 9:53 pm
I want to use this formula to calculate the following:
Age Groups:
0-13
14-17
18-65+
I tried =COUNTIF(A3:A30,”13″ for 14-17 years
and you may be able to see I’ll run into some trouble trying to use this to calculate the total amount of 18-65+ clients.
Any ideas?
Kind regards,
Topaz
July 24th, 2005 at 5:55 am
You are wanting to count by multiple criteria (greater than 13 and less than 18 is one age group), which countif isn’t good for.
Jwalk has a nice list of how to count by multiple criteria.
Here’s a sample formula that looks in A1:A10 and gives you the count of ages in the age group 14-17 (enter this with ctrl+shift+enter):
=SUM((A1:A10>=13)*(A1:A10<=18))
October 13th, 2008 at 2:05 pm
[...] in range in yellow. It does not matter whether the values are text or numbers. It uses Excel’s COUNTIF function to count up the duplicates and then sets the colour to [...]