There are many functions in Excel that contain “count” in the name. What are the differences?
In this blog post we’ll cover:
COUNT(): Returns a count of the number of cells that contain numbers
The picture above clearly shows a returned value of 3 for the range. Only 3 of the 5 cells contain numbers. This function is useful if you’re working with data sets with multiple types of data spread through a column or range.
COUNTA(): Returns the number of non-blank cells from a given range.
Based on the same data set, we get a COUNTA value of 5, because all 5 cells in our range have some value in them.
Let’s see what happens when we extend our range to F3:F10:
The value is still 5 because cells F8:F10 had nothing in them. COUNTA is a very useful formula for figuring out how many values are in a given data column. Many times we’ll have to paste similar data sets week to week, but the size of the data will change. One week you have 1,000 lines, the next week 1,312 lines. If your formulas depend on the number of rows of data, you may find yourself using COUNTA().
COUNTIF(): Returns the number of cells that match a given criteria.
The COUNTIF() formula above calculates how many employees had sales in excess of $10,000. The first part of the COUNTIF formula highlights the range we want to use, while the second part of the formula specifies the criteria. In this case, we would like to count the number of employees with greater than $10,000 in sales.
We then sorted the data and added conditional formatting to determining which employees were under performing compared to the rest:
COUNTIFS(): Return a count of cells that meet multiple criteria.
We are given a list of 75 branch offices, each from one of four regions:
We determine that the top performing branches are those that accumulate over $850,000 in sales for the year. Let’s create a formula that calculates how many branches from each region met this criteria:
The key with this formula is understanding that each criteria you specify comes in a pair:
- What is the range you want to search?
- What is the criteria?
In our case, we’re first looking in column C for a region that matches E2, “North”. Secondly, we search column B for values greater than 850,000. Our result is 2.
The net result of our formula is to combine the criteria of “Region” along with sales over $850,000. It looks like the East and South regions have outperformed the rest. Well done. Out of 75 branches, we’ve honed in on the top 15 branches.