COUNTIF
In its essence COUNTIF is an advanced version of the basic COUNT Function. Whereas with COUNT you simply specify a range of cells and ask Excel to tell you how many there are, this function allows you to specify additional criteria. You could, for example, ask Excel to tell you how many cells within a range are greater than 4 or contain the name James.
The basic formula is as follows:
=COUNTIF(RANGE,CRITERIA)
As with COUNT this formula can only be used with a single range of cells, however it is very useful nonetheless. Here’s an example dataset that might benefit from the use of COUNTIF:
There are a number of questions here that we can answer using the COUNTIF Function, for example:
How many days is James on call for?
How many times is a member of staff expected to be on call for 4 hours or more?
Since we already know how to select a range of cells for the first half of the formula, we’ll concentrate on the criteria.
In the above image I’ve given several examples of how you can specify the criteria you want to use. We have several basic options to choose from:
· Ask Excel to count the cells that are equal to an exact piece of text
· Ask Excel to count the cells that are equal to another cell (Text or number)
· Ask Excel to count the cells that meet the criteria of a specified formula
Any time you’re not using a cell reference as the criteria for a COUNTIF formula you will need to enclose your criteria in inverted comma – This is true even if you’re using a formula. This is unusual for Excel Formulas and it’s something that often catches people out.
In column C of the above example you’ll see that I’ve included two formulas that are both requesting the same information – How many times is James on call? Clearly the answer is 3, but let’s examine how the criteria have been put together.
The first criteria is simply “James”, there is no need to include an equals sign before this criteria. In this case we’re asking Excel to match cells in the specified range against an exact piece of text and tell us how many were identical. In the second example I’ve used a cell reference instead, F6, which also contains the word James. Again there is no need to include an equals sign, and of course the cell you’re referencing against could include whatever text or number you desire.
In column D I’ve again demonstrated that a cell reference can be used as criteria, in this case it’s F7 which contains the number 3. The second formula in this column is more interesting – I’ve asked Excel to tell me how many cells in the specified range are greater than or equal to 4. As explained above this formula must be enclosed in inverted commas. Here are a few examples of formulas you might want to use as criteria:
Equals: =
Greater than: >
Less than: <
Greater than or equal to >=
Less than or equal to <=
Does not equal: <>
The ‘Does not equal’ formula often catches people out because it’s not the standard mathematical symbol, which is ≠, and nor is it the standard programming symbol which is !=
So for example a complete formula might look like this:
=COUNTIF(D3:D9,<=4)
If we apply this to our example we’ll get the answer 2, because only rows 5 and 9 contain numbers which are either less than or equal to the number 4.
I hope you've found this page useful; I’d appreciate it if you’d take the time to let me know how you rate it.
If you’re still struggling with the COUNTIF Function you might find it helpful to have another look at the Getting started with Excel formulas page.
Looking for more? Return to Excel Formulas or the Office Software homepage
