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 present you a glimpse of how these capabilities work, consider a job the place it’s possible you’ll must scan an extended sheet of knowledge. You wouldn’t need to do that manually, after all, because it beats your entire objective of utilizing Excel (you would possibly 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 fundamental query…
What’s the COUNT Operate 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 spread 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 your entire vary that you just specify to test for numbers in an analogous method.
COUNT Syntax
With its fairly easy objective, the syntax of COUNT turns into tremendous straightforward:
=COUNT(value1, [value2], [value3], ...)The a number of values right here point out you could apply the identical COUNT perform throughout a number of values, cell references, or ranges.
Allow us to do this in follow by forming a brand new perform for extracting the depend from a spread of cells.
Writing the COUNT perform
To grasp easy methods to write the COUNT perform appropriately, contemplate the next desk for instance.
Suppose we want to know the variety of response time entries on this sheet, we will use the COUNT perform as follows:
=COUNT(C2:C8)
The components counts the variety of cells from C2 to C8 that comprise a numeric worth. That’s the reason the reply comes out as “4”. Be aware 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 full variety of tickets dealt with + the variety of response time entries. I can use the COUNT perform as follows:
=COUNT(C2:C8, D2:D8)
It will scan each Column A and Column C throughout the talked about cell ranges to depend the numbers. The full 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 example, we might merely need to know the variety of workers with a selected 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 Operate
What’s the COUNTIF Operate 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 spread that meet a selected 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 nearly 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 also be not too difficult:
=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 may increasingly look barely extra critical 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 Operate
Allow us to use the identical desk once more. Suppose we need to know what number of workers have their standing marked as Closed. We are able to write:
=COUNTIF(E2:E8, "Closed")
This components checks all cells from E2 to E8 and counts solely those who comprise the phrase Closed. Primarily based on the desk, the reply comes out to be 4.
Now suppose we need to depend the variety of workers who dealt with precisely 5 tickets. In that case, we will 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 need to know what number of workers had a response time larger than 10 minutes. We are able to write:
=COUNTIF(C2:C8, ">10")
This components checks the values within the Response Time column and counts solely these which can be larger than 10. The consequence right here is 3.
One factor to note rigorously: when utilizing operators like >, <, >=, or <=, your entire situation should go inside citation marks. Excel likes guidelines, and that is one in all them.
So sure, COUNTIF is mainly what you employ while you need counting with a filter connected. That can also be what makes it some of the sensible Excel capabilities on the market.
Although even this isn’t the height practicality that Excel gives. 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 Operate in Excel?
You may deduce it from the title – its a COUNTIF with an “s” on the finish – which means plural. COUNTIFS is used while you need 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 approach of claiming, “Be as particular as you need.”
This makes it particularly helpful when working with bigger datasets the place one situation is just not sufficient. As an example, it’s possible you’ll not simply need 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 seems to be barely longer, however the logic is repetitive:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)This implies:
- criteria_range1 is the primary vary Excel will test
- criteria1 is the situation for that first vary
- criteria_range2 is the second vary Excel will test
- criteria2 is the situation for that second vary
And so forth.
Sure, it seems to be like a kind of formulation that turns into annoying should you stare at it too lengthy. However when you see it in motion, it is vitally straightforward to comply with. Excel is mainly pairing every vary with a situation after which counting solely these rows the place all of the circumstances are true.
Writing the COUNTIFS Operate
Within the pattern desk above, suppose we need 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
Primarily based on our desk, the reply comes out to be 3.
Allow us to strive one other one.
Suppose we need to depend workers whose standing is Open and whose response time discipline is clean. We are able to write:
=COUNTIFS(E2:E8, "Open", C2:C8, "")
This components counts rows the place the standing is Open and the response time cell is empty. From our pattern knowledge, the consequence comes out to be 1.
That’s the actual energy of COUNTIFS. As a substitute of simply counting cells with values, it counts values with context. In brief, use COUNTIF when one situation is sufficient. Use COUNTIFS when your knowledge wants a bit of extra interrogation.
This additionally brings us to another capabilities inside the COUNT household that assist with particular circumstances. These are: COUNTA and COUNTBLANK
Additionally learn: Finest Assets to study Microsoft Excel
What’s the COUNTA Operate in Excel?
If COUNT solely counts cells with numbers, COUNTA is much less choosy. It counts all non-empty cells in a spread.
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 is just not clean. In brief, if one thing is sitting contained in the cell, COUNTA notices it.
This makes it helpful while you merely need to know what number of crammed entries exist in a column, no matter what sort of knowledge they comprise.
COUNTA Syntax
The syntax is sort of similar to COUNT:
=COUNTA(value1, [value2], [value3], ...)Identical to COUNT, you need to use it throughout a number of values, ranges, or cell references.
Writing the COUNTA Operate
Allow us to use the identical desk once more.
Suppose we need 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 components counts all non-empty cells from C2 to C8.
Now, not like 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 mainly counting every part that isn’t empty. Rather less judgmental, you might say.
What’s the COUNTBLANK Operate in Excel?
Now allow us to go within the precise wrong way. Whereas COUNTA counts crammed cells, COUNTBLANK counts the cells which can be empty in a given vary.
That is particularly helpful when you find yourself auditing knowledge and need to discover lacking entries. As a result of, allow us to be trustworthy, half of spreadsheet work is just not 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 normally works with a single vary argument.
Writing the COUNTBLANK Operate
Utilizing the identical desk, suppose we need to learn the way many response time entries are lacking. We are able to write:
=COUNTBLANK(C2:C8)
This components 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 consequence 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 Operate Household: At a Look
So collectively, all these COUNT capabilities cut up the work fairly neatly.
- COUNT counts numeric cells
- COUNTA counts non-empty cells
- COUNTBLANK counts empty cells
- COUNTIF counts cells that meet a selected situation
- COUNTIFS counts cells that meet a number of circumstances.
Conclusion
Simply because the title 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 objective of providing you with a stable quantity from as giant a dataset as you may probably 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 may share what you’d want to study subsequent by dropping us a remark under. Till then!
Login to proceed studying and revel in expert-curated content material.