Excel Formula Count
Are you trying to count values in your worksheet and don’t understand why the result is too high? Or are you looking for a method that excludes blank cells? Maybe you want to count only values which are greater than 1,000.
Excel gives you the power to count only the values that meet your criteria with five different count functions. So when do you use each one?
Excel Formula – COUNT
The COUNT function is the most basic of the five, and it’s the one you’ll call on most often. Use it to count the numeric values in a range. COUNT ignores blank cells, cells containing text, and cells containing errors.
In this example, the report includes ten metropolitan areas, though Albany has no sales. Because it has no sales, Albany’s Average Price is undefined. The COUNT function finds nine numeric values in each column, skipping the blank cells for Albany’s Units Sold and Sales Revenue and ignoring the text “N/A” for Albany’s Average Price.
Excel Formula – COUNTA
The COUNTA function is nearly identical to COUNT, but it picks up all values, including cells that contain text or error messages. Like COUNT, COUNTA ignores blank cells.
Like COUNT, COUNTA returns 9 for Units Sold and Sales Revenue because it ignores the blank cells for Albany. However, Albany’s Average Price is not blank, so COUNTA includes the text value “N/A” in its result. Note that a single space (“ ”) or an empty text string (“”) are not blank, so COUNTA will include them.
Excel Formula – COUNTBLANK
The complement of COUNTA, COUNTBLANK returns the number of cells which contain nothing at all—no numbers, no text, and no error messages.
In this example, the only two cells empty of data are Albany’s Units Sold and Sales Revenue. The COUNTBLANK finds both. On the other hand, every cell has an entry for Average Price, so COUNTBLANK returns 0.
COUNTBLANK is especially useful to test a dataset which should contain values in every cell, a dataset in which a blank cell might indicate an error. If COUNTBLANK returns any value greater than 0, this would be a signal to double-check the data for accuracy.
Excel Formula – COUNTIF
More complex than other variations of COUNT, COUNTIF returns the number of cells that meet a certain condition. The function takes two parameters:
1. The range to count; and
2. The condition which each value must meet if it is to be counted.
In this example, the formula says to count the Average Price values (E5:E14), but only those which are greater than 20. It finds four: Baltimore, Columbus, Philadelphia, and Pittsburgh.
Excel Formula – COUNTIFS
Finally, COUNTIFS is identical to COUNTIF except that it can test for more than one condition. It takes any number of parameters:
1. The first range to test;
2. The condition which each value in the first range must meet if it is to be counted;
3. The second range to test;
4. The condition which each value in the second range must meet if it is to be counted;
5. The third range to test;
6. The condition . . .
And so on.
This example tests for two conditions: Average Price (E5:E14) must be greater than 20, and Units Sold (C5:C14) must be greater than 1,000. The function finds only Columbus and Philadelphia that pass both tests.
This function is especially useful to find averages or other ratios that exceed a certain threshold, excluding those with only a few values to avoid skewing the ratios with statistically insignificant data.
Additional Options
Do you need even more powerful count functions? Try combining these functions SUMIF, AVERAGEIF, or any of the additional formulas that Excel provides.
