SEE: Google Work area vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
In this tutorial, I’ll show you how to successfully execute COUNTIF() using an easy sheet of sales and commission values. Particularly, we’ll compare the current year’s commissions to last year’s to see if anyone exceeded in 2015’s greatest commission. This indicates utilizing comparison operators, which needs a bit of specialized knowledge.
Prior to we begin, it is necessary to know that what you’re learning about revealing conditions in an Excel-friendly method does not only use to COUNTIF(). You can utilize what you find out here to enhance a variety of functions and expressions.
For this presentation, I’ll be using Microsoft 365 Desktop on a Windows 10 64-bit system, however you can utilize earlier versions of Excel also. Excel for the web completely supports COUNTIF(). To follow along with our specific examples, you can download the presentation file here.
Jump to:
Can the COUNTIF function recommendation a cell?
The basic answer is yes, COUNTIF() can be utilized to reference a cell. Comparable to other functions, COUNTIF() can reference cells or varieties. However, the condition is entirely another thing. If the condition is in a cell, you’re fine, however that typically won’t hold true. Prior to we tackle this problem, let’s look at COUNTIF()’s argument:
COUNTIF(range, requirements)
Here, “variety” is the value or values you’re counting and “requirements” is the condition that figures out whether or not the function consists of a value in the count. This function requires both arguments.
The variety argument can be numbers, arrays, a called range or a referral to a range which contains the values to be counted. It’s important to keep in mind that this function overlooks blank and text values.
The 2nd argument, requirements, can be a number, expression, text string or cell referral that specifies which cells to count. This is the condition argument that gets confusing– rarely will a cell include the condition currently revealed in a way that Excel can interpret. For example, let’s suppose you wish to count just those worths that are higher than or equivalent to 1,500. You can put 1,500 in an input cell, but how do you express the higher than part?
The longer response to the concern presented here is yes and no. We’ll expand on that in the next area.
How do I reference a cell utilizing COUNTIF?
What you’ll discover in this area isn’t unique to COUNTIF(). Other functions likewise evaluate conditional arguments, so you can use this next area’s information well beyond COUNTIF() functions.
Let’s expect you wish to count the variety of commissions that surpass the previous year’s top commission. Figure A reveals an easy sheet of 2022 to-date sales figures and commissions:
Figure A
We’ll count people who have a 2022 commission rate that’s higher than 2021’s greatest commission. 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 leading commission of$2,710.79. In Excel language, that’s > 2,710.79. With a fast glance at Figure A, it’s simple to see that 3 commissions are higher than last year’s top commission. Now let’s use COUNTIF()to
return the count. You might try the
following initially:=COUNTIF(Sales2022 [Comm], > L3)
Sales2022 [Comm] is the range F3: F12, using structured referencing, and > L3 is the requirements. Remember, L3 returns the highest commission from the previous year.
Repairing the comparison operator
Our COUNTIF() setup makes perfect sense, but sadly, Excel will reject this function because > L3 isn’t a legitimate reference. If you get rid of the > sign, Excel accepts the function and returns 0 due to the fact that no worth equates to the highest commission from last year, $2,710.79.
The issue is the contrast operator, >. Excel just doesn’t understand what to do with it, as it is. Excel needs that you utilize delimiters, so let’s try it again with this function:
=COUNTIF(Sales2022 [Comm], “> L3”)
Sadly, this function also returns 0 due to the fact that no worth in the Comm column equates to > L3. Excel assesses everything between the two delimiters as the string to match.
Let’s try something else. What takes place if you move > to the leading 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 excellent practice to reveal a condition in this method because it’s too easy to forget.
To fix this issue, we need to delimit > within the requirements argument as follows:
=COUNTIF(Sales2022 [Comm], “>”& L3)
As you can see in Figure B, we finally have the ideal count; three 2022 commissions are greater than in 2015’s greatest commission.
Figure B
How to delimit the comparison operator.
Introducing the concatenation operator Now there’s an extra character in the requirements argument you might not acknowledge: &. This is Excel’s concatenation operator; the term, concatenation, in this case, implies to combine in Excel. For our presentation, the argument
“>”& L3
examines to
> & L3
> 2710.79
Now the COUNTIF() function can count only those values in the Comm column that are higher than 2710.79. When this is established properly, 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 relatively basic to utilize– it’s the condition expressed in requirements that may trigger confusion. As pointed out, learning how to successfully utilize requirements in COUNTIF() will serve you well and apply to any function that has a criteria-type argument.
Referencing a cell or range is normally easy until you require to include a condition revealed by a contrast operator. Then, you need to know how to use delimiters and the concatenation operator, &. This tutorial is a great location to begin if you want to find out more about this topic: 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 material library here.
Read next: The 8 best options to Microsoft Project (Free & Paid) (TechRepublic)
