In our earlier article of the Excel 101 collection, we learnt all there may be about conditional logic and operators in Excel. These operators assist massively in capabilities like IF, AND, OR, and many others. Nonetheless, there may be one other household of capabilities that’s used massively by Excel customers and largely makes use of those operators to yield outcomes. That is precisely the set of capabilities we will study on this article. To call them, these capabilities are the COUNT, COUNTIF, and all of the associated formulation.
To provide you a glimpse of how these capabilities work, consider a process the place you might have to scan an extended sheet of knowledge. You wouldn’t wish to do that manually, after all, because it beats the complete goal of utilizing Excel (you may simply make a spreadsheet as an alternative). That is precisely the place the COUNT capabilities assist.
How? And the way are they used? All in time, starting with probably the most primary query…
What’s the COUNT Perform in Excel?
Let’s begin with the best one within the household – the COUNT perform in Excel. It’s used to depend the variety of cells in a variety that comprise numeric values. That’s it. No drama, no hidden twist.
If a cell incorporates a quantity, COUNT will depend it as 1. If it incorporates textual content, a clean, or random phrases you typed, Excel will politely ignore it. It is going to then scan the complete vary that you just specify to examine for numbers in an identical method.
COUNT Syntax
With its fairly simple goal, the syntax of COUNT turns into tremendous straightforward:
=COUNT(value1, [value2], [value3], …)
The a number of values right here point out which you can apply the identical COUNT perform throughout a number of values, cell references, or ranges.
Allow us to do that in apply by forming a brand new perform for extracting the depend from a variety of cells.
Writing the COUNT perform
To know how one can write the COUNT perform appropriately, think about the next desk for example.
Suppose we want to know the variety of response time entries on this sheet, we are able to use the COUNT perform as follows:
=COUNT(C2:C8)
The method counts the variety of cells from C2 to C8 that comprise a numeric worth. That’s the reason the reply comes out as “4”. Observe how the perform ignores cells with textual content or clean cells and solely focuses on the numbers of cells with numbers.
Now, suppose I want to know the whole variety of tickets dealt with + the variety of response time entries. I can use the COUNT perform as follows:
=COUNT(C2:C8, D2:D8)
This may scan each Column A and Column C throughout the talked about cell ranges to depend the numbers. The entire quantity now comes out to be “11”.
That is precisely how the COUNT perform can be utilized to map numeric entries throughout numerous worth units.
However what if there’s a situation right here? As an illustration, we could merely wish to know the variety of workers with a specific response time or the variety of tickets dealt with. That’s the place COUNTIF is available in.
Additionally learn: Excel 101: Full Information to VLOOKUP Perform
What’s the COUNTIF Perform in Excel?
That is the place the COUNT perform will get a large practicality achieve. Whereas COUNT solely checks whether or not a cell incorporates a quantity, COUNTIF does one thing extra helpful. It counts the variety of cells in a variety that meet a particular situation.
In easy phrases, COUNT asks, “Is that this a quantity?”
COUNTIF asks, “Does this cell match what I’m in search of?”
That “situation” might be virtually something: a phrase, a quantity, a comparability, or perhaps a worth from one other cell. So if COUNT is the fundamental counter, COUNTIF is the marginally smarter cousin who truly listens to directions.
COUNTIF Syntax
The syntax of COUNTIF can be not too sophisticated:
=COUNTIF(vary, standards)
Right here:
- vary is the group of cells Excel must scan
- standards is the situation that tells Excel what to depend
- This standards might be:
– a textual content worth like “Closed”
– a quantity like 5
– a situation like “>10”
– or perhaps a cell reference joined with an operator
At first look, this will likely look barely extra severe than COUNT, however it’s nonetheless very manageable. When you write it a few times, it stops wanting like Excel wizardry and begins wanting like frequent sense.
Writing the COUNTIF Perform
Allow us to use the identical desk once more. Suppose we wish to know what number of workers have their standing marked as Closed. We are able to write:
=COUNTIF(E2:E8, “Closed”)
This method checks all cells from E2 to E8 and counts solely people who comprise the phrase Closed. Based mostly on the desk, the reply comes out to be 4.
Now suppose we wish to depend the variety of workers who dealt with precisely 5 tickets. In that case, we are able to write:
=COUNTIF(D2:D8, 5)
This scans the Tickets Dealt with column and returns the depend of cells containing the worth 5. In our desk, the reply is 2.
Issues get much more helpful when numbers are concerned with circumstances.
Suppose we wish to know what number of workers had a response time larger than 10 minutes. We are able to write:
=COUNTIF(C2:C8, “>10”)
This method checks the values within the Response Time column and counts solely these which might be larger than 10. The outcome right here is 3.
One factor to note rigorously: when utilizing operators like >, <, >=, or <=, the complete situation should go inside citation marks. Excel likes guidelines, and that is one in all them.
So sure, COUNTIF is principally what you utilize if you need counting with a filter connected. That can be what makes it one of the sensible Excel capabilities on the market.
Although even this isn’t the height practicality that Excel presents. What if you wish to see entries that meet not one however two or extra standards? Enter COUNTIFS
Additionally learn: Microsoft Excel for Knowledge Evaluation
What’s the COUNTIFS Perform in Excel?
You possibly can deduce it from the identify – its a COUNTIF with an “s” on the finish – which means plural. COUNTIFS is used if you wish to depend the variety of cells or rows that fulfill a number of circumstances on the identical time.
In different phrases, if COUNTIF works with one rule, COUNTIFS works with two, three, or extra. It’s Excel’s means of claiming, “Be as particular as you need.”
This makes it particularly helpful when working with bigger datasets the place one situation is solely not sufficient. As an illustration, you might not simply wish to depend workers with a Closed standing, however particularly those that are Closed and have dealt with greater than 4 tickets. That’s the place COUNTIFS turns into much more sensible than COUNTIF.
COUNTIFS Syntax
The syntax right here appears barely longer, however the logic is repetitive:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)
This implies:
- criteria_range1 is the primary vary Excel will examine
- criteria1 is the situation for that first vary
- criteria_range2 is the second vary Excel will examine
- criteria2 is the situation for that second vary
And so forth.
Sure, it appears like a kind of formulation that turns into annoying if you happen to stare at it too lengthy. However when you see it in motion, it is vitally straightforward to comply with. Excel is principally pairing every vary with a situation after which counting solely these rows the place all of the circumstances are true.
Writing the COUNTIFS Perform
Within the pattern desk above, suppose we wish to know what number of workers have their standing marked as Closed and have dealt with greater than 4 tickets. We are able to write:
=COUNTIFS(E2:E8, “Closed”, D2:D8, “>4”)
Here’s what occurs:
- Excel first checks the cells in E2:E8 for the phrase Closed
- Then it checks the corresponding cells in D2:D8 for values larger than 4
- It counts solely these rows the place each circumstances are glad
Based mostly on our desk, the reply comes out to be 3.
Allow us to strive one other one.
Suppose we wish to depend workers whose standing is Open and whose response time subject is clean. We are able to write:
=COUNTIFS(E2:E8, “Open”, C2:C8, “”)
This method counts rows the place the standing is Open and the response time cell is empty. From our pattern knowledge, the outcome comes out to be 1.
That’s the actual power of COUNTIFS. As a substitute of simply counting cells with values, it counts values with context. Briefly, use COUNTIF when one situation is sufficient. Use COUNTIFS when your knowledge wants a little bit extra interrogation.
This additionally brings us to another capabilities throughout the COUNT household that assist with particular circumstances. These are: COUNTA and COUNTBLANK
Additionally learn: Finest Sources to study Microsoft Excel
What’s the COUNTA Perform in Excel?
If COUNT solely counts cells with numbers, COUNTA is much less choosy. It counts all non-empty cells in a variety.
So whether or not a cell incorporates a quantity, textual content, a logical worth, and even an error, COUNTA will depend it so long as the cell shouldn’t be clean. Briefly, if one thing is sitting contained in the cell, COUNTA notices it.
This makes it helpful if you merely wish to know what number of crammed entries exist in a column, no matter what sort of knowledge they comprise.
COUNTA Syntax
The syntax is almost similar to COUNT:
=COUNTA(value1, [value2], [value3], …)
Identical to COUNT, you should utilize it throughout a number of values, ranges, or cell references.
Writing the COUNTA Perform
Allow us to use the identical desk once more.
Suppose we wish to depend what number of response time entries are current within the sheet, no matter whether or not they’re numbers or textual content. We are able to write:
=COUNTA(C2:C8)
This method counts all non-empty cells from C2 to C8.
Now, in contrast to COUNT, this perform will embody:
- numeric values like 12, 18, 9, and 15
- textual content values like Delayed and Pending
It is going to ignore solely the clean cell. That’s the reason the reply right here comes out to be 6.
So if COUNT is selective, COUNTA is principally counting the whole lot that’s not empty. Rather less judgmental, you may say.
What’s the COUNTBLANK Perform in Excel?
Now allow us to go within the precise other way. Whereas COUNTA counts crammed cells, COUNTBLANK counts the cells which might be empty in a given vary.
That is particularly helpful when you find yourself auditing knowledge and wish to discover lacking entries. As a result of, allow us to be trustworthy, half of spreadsheet work shouldn’t be evaluation. It’s discovering who forgot to fill what.
COUNTBLANK Syntax
The syntax is even easier:
=COUNTBLANK(vary)
Not like COUNT or COUNTA, this perform often works with a single vary argument.
Writing the COUNTBLANK Perform
Utilizing the identical desk, suppose we wish to learn how many response time entries are lacking. We are able to write:
=COUNTBLANK(C2:C8)
This method scans the cells from C2 to C8 and counts solely the clean ones.
In our desk, just one response time entry is empty, so the outcome comes out to be 1.
That’s the job of COUNTBLANK in a single line: it helps you measure what’s lacking, not what’s current.
The COUNT Perform Household: At a Look
So collectively, all these COUNT capabilities break up the work fairly neatly.
- COUNT counts numeric cells
- COUNTA counts non-empty cells
- COUNTBLANK counts empty cells
- COUNTIF counts cells that meet a particular situation
- COUNTIFS counts cells that meet a number of circumstances.
Conclusion
Simply because the identify suggests, the first perform of the COUNT set of capabilities is to “depend”. The distinction lies in what to depend. Whereas one perform counts numeric values, others have their very own standards of counting cells. On the finish, all serve the identical goal of providing you with a stable quantity from as giant a dataset as you possibly can presumably work on.
I hope this text made it simpler so that you can perceive all of the COUNT capabilities. We will comply with this up with one other Excel performance quickly. Until then, you possibly can share what you’d want to study subsequent by dropping us a remark under. Till then!
Technical content material strategist and communicator with a decade of expertise in content material creation and distribution throughout nationwide media, Authorities of India, and personal platforms
Login to proceed studying and luxuriate in expert-curated content material.
Maintain Studying for Free
