Counting values is an easy task using Excel’s COUNTIF() function, which is essentially a combination of the COUNT() and IF() functions. It is used to count the number of values in a range that meet a specific condition. To make it work, you supply the range and a condition, which can be a literal value or an expression.
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
In this tutorial, I’ll show you how to effectively implement COUNTIF() using a simple sheet of sales and commission values. Specifically, we’ll compare the current year’s commissions to last year’s to see if anyone surpassed last year’s highest commission. This means using comparison operators, which requires a bit of specialized knowledge.
Before we get started, it’s important to know that what you’re learning about expressing conditions in an Excel-friendly way doesn’t only apply to COUNTIF(). You can use what you learn here to optimize a variety of functions and expressions.
For this demonstration, I’ll be using Microsoft 365 Desktop on a Windows 10 64-bit system, but you can use earlier versions of Excel as well. Excel for the web fully supports COUNTIF(). To follow along with our specific examples, you can download the demonstration file here.
Jump to:
- Can the COUNTIF function reference a cell?
- How do I reference a cell using COUNTIF?
- Troubleshooting the comparison operator
- Other Excel tutorials and resources
Can the COUNTIF function reference a cell?
The simple answer is yes, COUNTIF() can be used to reference a cell. Similar to other functions, COUNTIF() can reference cells or ranges. However, the condition is altogether another thing. If the condition is in a cell, you’re fine, but that often won’t be the case. Before we tackle this problem, let’s look at COUNTIF()’s argument:
COUNTIF(range, criteria)
Here, “range” is the value or values you’re counting and “criteria” is the condition that determines whether or not the function includes a value in the count. This function requires both arguments.
The range argument can be numbers, arrays, a named range or a reference to a range that contains the values to be counted. It’s important to note that this function ignores blank and text values.
The second argument, criteria, can be a number, expression, text string or cell reference that specifies which cells to count. This is the condition argument that gets confusing — rarely will a cell contain the condition already expressed in a way that Excel can interpret. For instance, let’s suppose you want to count only those values that are greater than or equal to 1,500. You can put 1,500 in an input cell, but how do you express the greater than part?
The longer answer to the question posed here is yes and no. We’ll expand on that in the next section.
How do I reference a cell using COUNTIF?
What you’ll learn in this section isn’t unique to COUNTIF(). Other functions also evaluate conditional arguments, so you can apply this next section’s information well beyond COUNTIF() functions.
Let’s suppose you want to count the number of commissions that surpass the previous year’s top commission. Figure A shows a simple sheet of 2022 to-date sales figures and commissions:
Figure A
Although out of sight, there’s another sheet for 2021. The expression in K3
=’2021′!K3
returns the highest commission for 2021. We now have everything we need to count the number of commissions that are greater than last year’s top commission of $2,710.79. In Excel language, that’s >2,710.79.
With a quick glance at Figure A, it’s easy to see that three commissions are higher than last year’s top commission. Now let’s use COUNTIF() to return the count.
You might try the following first:
=COUNTIF(Sales2022[Comm],>L3)
Sales2022[Comm] is the range F3:F12, using structured referencing, and >L3 is the criteria. Remember, L3 returns the highest commission from the previous year.
Troubleshooting the comparison operator
Our COUNTIF() setup makes perfect sense, but unfortunately, Excel will reject this function because >L3 isn’t a valid reference. If you remove the > sign, Excel accepts the function and returns 0 because no value equals the highest commission from last year, $2,710.79.
The problem is the comparison operator, >. Excel simply doesn’t know what to do with it, as it is. Excel requires that you use delimiters, so let’s try it again with this function:
=COUNTIF(Sales2022[Comm], “>L3”)
Unfortunately, this function also returns 0 because no value in the Comm column equals >L3. Excel evaluates everything between the two delimiters as the string to match.
Let’s try something else. What happens if you move > to the top commission for 2021 in L3, like so:
=>’2021′!K3
Excel doesn’t like that either. Again, you have an undelimited string character, “>”. You could make it work, but it’s not a generally great practice to express a condition in this way because it’s too easy to forget.
To fix this problem, we need to delimit > within the criteria argument as follows:
=COUNTIF(Sales2022[Comm], “>”&L3)
As you can see in Figure B, we finally have the right count; three 2022 commissions are higher than last year’s highest commission.
Figure B
Introducing the concatenation operator
Now there’s an extra character in the criteria argument you might not recognize: &. This is Excel’s concatenation operator; the term, concatenation, in this case, means to combine in Excel. For our demonstration, the argument
“>”&L3
evaluates to
>&L3
> 2710.79
Now the COUNTIF() function can count only those values in the Comm column that are greater than 2710.79. Once this is set up correctly, it should show that three commissions to date are higher than the previous year’s highest commission.
Other Excel tutorials and resources
Overall, the COUNTIF() function is fairly simple to use — it’s the condition expressed in criteria that might cause confusion. As mentioned, learning how to effectively use criteria in COUNTIF() will serve you well and apply to any function that has a criteria-type argument.
Referencing a cell or range is usually easy until you need to include a condition expressed by a comparison operator. Then, you need to know how to use delimiters and the concatenation operator, &. This tutorial is a great place to start if you want to learn more about this subject: How to concatenate values in a single Excel column to a single row.
For other Excel tutorials and resources offered by TechRepublic, you can check out our Microsoft content library here.
Read next: The 8 best alternatives to Microsoft Project (Free & Paid) (TechRepublic)