Intro to the COUNTIF Function in Excel
Much of spreadsheet data is a collection of individual transactions or instances that don’t begin to offer meaning until they are organized and summarized in useful ways. Enter Excel’s handy COUNTIF function. When categories of data are mixed together, or if you wish to understand your data based on certain criteria, COUNTIF can help you sort the needed data from the rest or tally only information that falls into specified ranges. When combined with other functions, COUNTIF can become even more useful.
In this example we will show how to count data that falls within a certain range that we specify. Our table shows the monthly levels of contaminates present in an imaginary water sample taken over three years. We want to know how many months in that timeframe the levels were higher than 3.0 PPM (our “warning” level) and how many months the levels fell below 1.0 PPM.
To follow using our example, download Countif Function Excel. These examples apply to Excel 2007 and higher. Images were taken using Excel 2013 on Windows 7.
Count Cells That Match a Specified Criteria
The syntax for the COUNTIF function is COUNTIF(range, criteria) where
- The range argument defines the group of cells that you want counted.
- The criteria argument defines the circumstances under which a cell will be added to your count. The criteria can be a number, a cell reference, an expression or a text string.
In our example, we will place this example in E4:
- B3:B43 tells the function to count cells in our “Amount” column
- “>3.0” tells the function to only count cells if they are greater than 3.0
In the same way, the formula that will go in E5 will be:
Use COUNTIF in Formulas
Now that we have COUNTIF working for us, we can combine it with other functions for even more data crunching. Using this same example, we can find out how many months fell between our high and low criteria by subtracting those results from the total. To do this we will combine the simple COUNT function with the functions we just specified to subtract them from the total number of months in our table:
- COUNT(B3:B43) gives us the total number of rows/months
- (COUNTIF(B3:B43,”>3.0″)+COUNTIF(B3:B43,”<1.0″)) adds together the number of rows/months over 3.0 and under 1.0 which are subtracted from the total number of rows.
In this particular example we could have used the COUNTIFS function to specify a count of the rows between the two specified numbers. That formula would look like:
However, it does illustrate how you can use COUNTIF in formulas for data that isn’t numerical, for example, or if you are using a version older than Excel 2007.
Bonus Hint!
If you need to count data in a table that frequently changes – such as new rows added every month – then use a Named Range or a dynamic named range in the range argument. When your table changes, you only have to update the range in one place instead of each of the many places the range is used.
Here is a quick list of tasks that you can use COUNTIF to help you perform:
- Count cells that have specific numerical relationships such as between two numbers (as above), equal numbers, positive or negative numbers, numbers that are greater and less than a specified value or are specifically not
- Count cells that begin or end with a specified criteria
- Count cells that contain specific text or a certain number of characters
- Count matches in two ranges
- Find duplicates
What are some ways you have used COUNTIF to get the most out of your data?
