Video extract as seen on YouTube, plus extended tutorial below.
|Click the Full Screen button on the player to watch it in HD.|
In this tutorial we’re looking at the COUNTIF and COUNTIFS Formulas, and we’ll take a look at a couple of different applications for them.
Plus download a practice workbook. Note: this is an Excel .xlsx workbook. Please ensure your browser doesn’t change the file extension on download.
The COUNTIF/S functions work ALMOST in the same way as the SUMIF/S functions only they’re slightly simpler. So if you haven’t mastered SUMIF/S yet be sure to check out our SUMIF/S tutorial too.
COUNTIF extends the capabilities of the basic COUNT function by allowing you to tell Excel to only COUNT items that meet a certain criteria. New in Excel 2007 is the COUNTIFS function, which allows you to stipulate multiple criteria, hence the plural.
Enough explanation, let’s dive into an example as it’s easier to visualise.
The function wizard in Excel describes COUNTIF as:
Looks fairly simple and it is. Let’s translate it into English now by applying it to an example. Say we wanted to count the number of times Dave appeared in column C of the table below.
Translated our formula would read like this:
=COUNTIF(count the number of cells in column C, that contain 'Dave')
We could even create a table under the data to count the occurrences of each builder:
Our formula in cell C12 would be:
While the above formula is good, if we were to copy it to the rest of the summary table (cells C11 to C14) we would have to manually change the cell references and builder’s name to get the correct answers.
To avoid this manual intervention, we can use absolute references, which will speed up the process of copying the formula to the remainder of column C.
With absolute references our formula would look like this:
The ‘$’ signs tell Excel that we don’t want the reference after the ‘$’ sign to change when we copy the formula. For example, if we copied the formula into cell C13 it would read:
In the above formula we can see that the only reference that changed was $B12, which became $B13.
The best way to understand how this works is to try it for yourself. Download the workbook used in this example here.
Note: this is an Excel .xlsx workbook. Please ensure your browser doesn’t change the file extension on download.
Note: I used a basic example to illustrate how to use COUNTIF, but you could also achieve this count by builder using the subtotal tool in the Data tab.
The function wizard in Excel describes COUNTIFS as:
=COUNTIFS(critera_range_1,criteria_1,criteria_range_2,criteria_2.....and so on if required)
Extending the previous COUNTIF example above, say we wanted to only summarise the data by builder for jobs in the South region. We could use the COUNTIFS function, as it allows us to set more than one condition.
Here’s how the formula would be interpreted if we wanted to count the occurrences of Brian in column C, where jobs were in the South region:
=COUNTIFS(count the number of cells in column C if, they contain ‘Brian’ and, if in column B, they are also for the South region)
Note: Excel will only include the cells in column C in the count when both conditions (Brian & South) are met.
Using absolute references, our COUNTIFS formula in Excel cell C20 would read:
Try other operators
Other operators you could use are:
- = Equal to
- < Less Than
- > Greater Than
- <= Less than or equal to
- >= Greater than or equal to
- <> Less than or greater than
For example, if you wanted to count the jobs with an average > $300k the formula would be:
Note: again I’ve used a simple example to illustrate this, but another way to achieve this summary table for each builder by region is to use a Pivot Table.
Want to Learn More Excel Formulas
Why not visit our list of Excel formulas. You’ll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy 🙂
Spread the Word
If you found this useful please share it with your friends and colleagues on Google+1, LinkedIn, Facebook and Twitter.