In our previous article of the Excel 101 series, we learnt all there is about conditional logic and operators in Excel. These operators help massively in functions like IF, AND, OR, etc. However, there is another family of functions that is used massively by Excel users and largely makes use of these operators to yield results. This is exactly the set of functions we shall learn in this article. To name them, these functions are the COUNT, COUNTIF, and all the related formulas.
To give you a glimpse of how these functions work, think of a task where you may need to scan a long sheet of data. You would not want to do this manually, of course, as it beats the entire purpose of using Excel (you might just make a spreadsheet instead). This is exactly where the COUNT functions help.
How? And how are they used? All in time, beginning with the most basic question…
What is the COUNT Function in Excel?
Let’s start with the easiest one in the family – the COUNT function in Excel. It is used to count the number of cells in a range that contain numeric values. That is it. No drama, no hidden twist.
If a cell contains a number, COUNT will count it as 1. If it contains text, a blank, or random words you typed, Excel will politely ignore it. It will then scan the entire range that you specify to check for numbers in a similar manner.
COUNT Syntax
With its pretty straightforward purpose, the syntax of COUNT becomes super easy:
=COUNT(value1, [value2], [value3], ...)The multiple values here indicate that you can apply the same COUNT function across multiple values, cell references, or ranges.
Let us try this in practice by forming a new function for extracting the count from a range of cells.
Writing the COUNT function
To understand how to write the COUNT function correctly, consider the following table as an example.
Suppose we wish to know the number of response time entries in this sheet, we can use the COUNT function as follows:
=COUNT(C2:C8)
The formula counts the number of cells from C2 to C8 that contain a numeric value. That is why the answer comes out as “4”. Note how the function ignores cells with text or blank cells and only focuses on the numbers of cells with numbers.
Now, suppose I wish to know the total number of tickets handled + the number of response time entries. I can use the COUNT function as follows:
=COUNT(C2:C8, D2:D8)
This will scan both Column A and Column C across the mentioned cell ranges to count the numbers. The total number now comes out to be “11”.
This is exactly how the COUNT function can be used to map numeric entries across various value sets.
But what if there is a condition here? For instance, we may simply want to know the number of employees with a particular response time or the number of tickets handled. That is where COUNTIF comes in.
Also read: Excel 101: Complete Guide to VLOOKUP Function
What is the COUNTIF Function in Excel?
This is where the COUNT function gets a massive practicality gain. While COUNT only checks whether a cell contains a number, COUNTIF does something more useful. It counts the number of cells in a range that meet a specific condition.
In simple words, COUNT asks, “Is this a number?”
COUNTIF asks, “Does this cell match what I am looking for?”
That “condition” can be almost anything: a word, a number, a comparison, or even a value from another cell. So if COUNT is the basic counter, COUNTIF is the slightly smarter cousin who actually listens to instructions.
COUNTIF Syntax
The syntax of COUNTIF is also not too complicated:
=COUNTIF(range, criteria)Here:
- range is the group of cells Excel needs to scan
- criteria is the condition that tells Excel what to count
- This criteria can be:
– a text value like “Closed”
– a number like 5
– a condition like “>10”
– or even a cell reference joined with an operator
At first glance, this may look slightly more serious than COUNT, but it is still very manageable. Once you write it once or twice, it stops looking like Excel wizardry and starts looking like common sense.
Writing the COUNTIF Function
Let us use the same table again. Suppose we want to know how many employees have their status marked as Closed. We can write:
=COUNTIF(E2:E8, "Closed")
This formula checks all cells from E2 to E8 and counts only those that contain the word Closed. Based on the table, the answer comes out to be 4.
Now suppose we want to count the number of employees who handled exactly 5 tickets. In that case, we can write:
=COUNTIF(D2:D8, 5)
This scans the Tickets Handled column and returns the count of cells containing the value 5. In our table, the answer is 2.
Things get even more useful when numbers are involved with conditions.
Suppose we want to know how many employees had a response time greater than 10 minutes. We can write:
=COUNTIF(C2:C8, ">10")
This formula checks the values in the Response Time column and counts only those that are greater than 10. The result here is 3.
One thing to notice carefully: when using operators like >, <, >=, or <=, the entire condition must go inside quotation marks. Excel likes rules, and this is one of them.
So yes, COUNTIF is basically what you use when you want counting with a filter attached. That is also what makes it one of the most practical Excel functions out there.
Though even this is not the peak practicality that Excel offers. What if you want to see entries that meet not one but two or more criteria? Enter COUNTIFS
Also read: Microsoft Excel for Data Analysis
What is the COUNTIFS Function in Excel?
You can deduce it from the name – its a COUNTIF with an “s” at the end – meaning plural. COUNTIFS is used when you want to count the number of cells or rows that satisfy multiple conditions at the same time.
In other words, if COUNTIF works with one rule, COUNTIFS works with two, three, or more. It is Excel’s way of saying, “Be as specific as you want.”
This makes it especially useful when working with larger datasets where one condition is simply not enough. For instance, you may not just want to count employees with a Closed status, but specifically those who are Closed and have handled more than 4 tickets. That is where COUNTIFS becomes far more practical than COUNTIF.
COUNTIFS Syntax
The syntax here looks slightly longer, but the logic is repetitive:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)This means:
- criteria_range1 is the first range Excel will check
- criteria1 is the condition for that first range
- criteria_range2 is the second range Excel will check
- criteria2 is the condition for that second range
And so on.
Yes, it looks like one of those formulas that becomes annoying if you stare at it too long. But once you see it in action, it is very easy to follow. Excel is basically pairing each range with a condition and then counting only those rows where all the conditions are true.
Writing the COUNTIFS Function
In the sample table above, suppose we want to know how many employees have their status marked as Closed and have handled more than 4 tickets. We can write:
=COUNTIFS(E2:E8, "Closed", D2:D8, ">4")
Here is what happens:
- Excel first checks the cells in E2:E8 for the word Closed
- Then it checks the corresponding cells in D2:D8 for values greater than 4
- It counts only those rows where both conditions are satisfied
Based on our table, the answer comes out to be 3.
Let us try another one.
Suppose we want to count employees whose status is Open and whose response time field is blank. We can write:
=COUNTIFS(E2:E8, "Open", C2:C8, "")
This formula counts rows where the status is Open and the response time cell is empty. From our sample data, the result comes out to be 1.
That is the real strength of COUNTIFS. Instead of just counting cells with values, it counts values with context. In short, use COUNTIF when one condition is enough. Use COUNTIFS when your data needs a little more interrogation.
This also brings us to some other functions within the COUNT family that help with specific conditions. These are: COUNTA and COUNTBLANK
Also read: Best Resources to learn Microsoft Excel
What is the COUNTA Function in Excel?
If COUNT only counts cells with numbers, COUNTA is less picky. It counts all non-empty cells in a range.
So whether a cell contains a number, text, a logical value, or even an error, COUNTA will count it as long as the cell is not blank. In short, if something is sitting inside the cell, COUNTA notices it.
This makes it useful when you simply want to know how many filled entries exist in a column, regardless of what kind of data they contain.
COUNTA Syntax
The syntax is nearly identical to COUNT:
=COUNTA(value1, [value2], [value3], ...)Just like COUNT, you can use it across multiple values, ranges, or cell references.
Writing the COUNTA Function
Let us use the same table again.
Suppose we want to count how many response time entries are present in the sheet, regardless of whether they are numbers or text. We can write:
=COUNTA(C2:C8)
This formula counts all non-empty cells from C2 to C8.
Now, unlike COUNT, this function will include:
- numeric values like 12, 18, 9, and 15
- text values like Delayed and Pending
It will ignore only the blank cell. That is why the answer here comes out to be 6.
So if COUNT is selective, COUNTA is basically counting everything that is not empty. A little less judgmental, you could say.
What is the COUNTBLANK Function in Excel?
Now let us go in the exact opposite direction. While COUNTA counts filled cells, COUNTBLANK counts the cells that are empty in a given range.
This is especially useful when you are auditing data and want to find missing entries. Because, let us be honest, half of spreadsheet work is not analysis. It is discovering who forgot to fill what.
COUNTBLANK Syntax
The syntax is even simpler:
=COUNTBLANK(range)Unlike COUNT or COUNTA, this function usually works with a single range argument.
Writing the COUNTBLANK Function
Using the same table, suppose we want to find out how many response time entries are missing. We can write:
=COUNTBLANK(C2:C8)
This formula scans the cells from C2 to C8 and counts only the blank ones.
In our table, only one response time entry is empty, so the result comes out to be 1.
That is the job of COUNTBLANK in one line: it helps you measure what is missing, not what is present.
The COUNT Function Family: At a Glance
So together, all these COUNT functions split the work quite neatly.
- COUNT counts numeric cells
- COUNTA counts non-empty cells
- COUNTBLANK counts empty cells
- COUNTIF counts cells that meet a specific condition
- COUNTIFS counts cells that meet multiple conditions.
Conclusion
Just as the name suggests, the primary function of the COUNT set of functions is to “count”. The difference lies in what to count. While one function counts numeric values, others have their own criteria of counting cells. At the end, all serve the same purpose of giving you a solid number from as large a dataset as you can possibly work on.
I hope this article made it easier for you to understand all the COUNT functions. We shall follow this up with another Excel functionality soon. Till then, you can share what you would wish to learn next by dropping us a comment below. Until then!
Login to continue reading and enjoy expert-curated content.
