Q. I am trying to do some analysis regarding how many clients each of our departments serviced and how many of those clients were billed over a certain amount. I have our department/ client information in Excel. Is there an easy way to do this?
A. This type of analysis can easily be done using COUNTIF and COUNTIFS. COUNTIF is used to count items based on one criterion, while COUNTIFS is used to count items based on multiple criteria. Other types of inquiries that these two functions could answer are “How many students received an A in the class?,” “How many employees’ salaries are at least $60,000?,” “How many participants are 60 years or older?,” or “How many of the shirts we have in inventory are blue?” I have created a workbook with examples of using COUNTIF and COUNTIFS. You can also view a video demonstration at the end of this article.
Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
Let’s look at some examples of how to use COUNTIF and COUNTIFS. First, let’s determine how many clients our tax department services from the dataset shown in the screenshot below.
This is a relatively small dataset for demonstration purposes, and you could manually determine the answer fairly quickly, but the function works just as easily for any size dataset. To determine how many clients the tax department services in this dataset, we will use the function COUNTIF. This is because we want to count the number of times the word “Tax” is listed under the department column, which is the only criterion we are looking for. There are only two arguments to define for COUNTIF: Range and Criteria. The input for the Range argument is the range of cells that contains the criteria. The input for the Criteria argument is the text, number, or expression that can be found in the defined range. In our example, the Range is D2:D7 since that is the range of cells where our department names are located. The Criteria is the text "Tax". The formula is =COUNTIF(D2:D7,"Tax"). The resulting value gives us the answer of three; the tax department services three clients.
Next, let’s answer the question, “How many clients did the tax department bill more than $5,000?,” using the same dataset as our previous example. To answer this question, we will use the function COUNTIFS because we will need Excel to do a count based on two criteria: that the department is tax and the amount billed to the client is greater than $5,000.
With COUNTIFS, there are two arguments to define for each criterion. You could use COUNTIFS if you only had one criterion, and then you would only need to define two arguments. However, if you had two criteria, you would define four arguments, and so on. The required arguments are Criteria_range1 and Criteria1, with optional arguments being Criteria_range2 and Criteria2, Criteria_range3 and Criteria3, etc., depending on how many criteria you have.
The input for the Criteria_range argument is the range of cells that contains the criteria. The input for the Criteria argument is the text, number, or expression that can be found in the defined range. In our example, the Criteria_range1 is D2:D7 since that is the range of cells where our department names are located. Criteria1 is the text "Tax". The Criteria_range2 is J2:J7 since that is the range of cells where our amounts billed to client are located, and the Criteria2 is ">5000". The formula is =COUNTIFS(D2:D7,"Tax",J2:J7,">5000"). The resulting value gives us the answer of two; the tax department bills more than $5,000 to two clients.
Now let’s look at a different dataset and answer some questions. See the screenshot below for a portion of a list of inventory with colors and price data.
The first question we will answer is, “How many blue shirts do we have in inventory?” Since there is only one criterion to count, we will use COUNTIF. The Range is A3:A27 since that is the range of cells where the color blue is located. The Criteria is the text "Blue". The formula is =COUNTIF(A3:A27,"Blue"). The resulting value gives us the answer of 12; we have 12 blue shirts in inventory.
Now let’s answer the question, “How many blue shirts that sell for over $40 do we have in inventory?” We will use COUNTIFS since we have more than one criteria: color and price. The Criteria_range1 is A3:A27, Criteria1 is "Blue", Criteria_range2 is B3:B27, and the Criteria2 is ">40". The formula is =COUNTIFS(A3:A27,"Blue",B3:B27,">40"). The resulting value gives us the answer of 10; we have 10 blue shirts that sell for over $40 in inventory.
Let’s finish up with one more example so that you can see how COUNTIFS works with even more than two criteria. COUNTIFS can actually handle up to 127 criteria. We will use the dataset in the screenshot below that contains information about our real estate properties.
We will answer the question, “How many real estate properties sold in the month of August with three bedrooms, more than 1,225 square feet, and above list price?” We will use COUNTIFS since we have more than one criteria: month of sale, number of bedrooms, square footage, and the difference between list price and sales price. Criteria_range1 is G3:G14, Criteria1 is "August", Criteria_range2 is B3:B14, Criteria2 is 3, Criteria_range3 is D3:D14, Criteria3 is ">1225", Criteria_range4 is H3:H14, and Criteria4 is ">0". The formula is =COUNTIFS (G3:G14,"August",B3:B14,3,D3:D14,">1225",H3 :H14,">0"). The resulting value gives us the answer of one; we have one real estate property that sold in the month of August with three bedrooms, more than 1,225 square feet, and above list price.
Using a formula to answer these types of questions is faster than manually identifying the answers, and it reduces the risk of human error.
To do similar types of analyses where you need to sum the amounts instead of count, refer to the articles about SUMIF and SUMIFS.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
