Excel COUNT vs COUNTA: how to sum the number of cells with text or just numbers? What’s the difference between COUNT and COUNTA functions and how to use them?
In this free Excel tutorial, we’ll explore COUNT function, COUNTA function and will share with you the Excel formula to count empty cells.
Are you ready to master MS Excel worksheets?
What is COUNT in Excel?
Excel COUNT lets you calculate the number of cells only containing numbers within a specific range.
The Excel COUNT formula is =COUNT(value1, value2,…) with value1 being the only required argument.
value1 — it can be a cell reference or a specific range.
value2,… — you can add up to 256 arguments (values) in the formula.
COUNT function tips
– All arguments representing numbers will be counted as numbers: numbers, dates, error values translated into numbers (for example if VLOOKUP results in 0), etc.
– You can identify numbers in Excel easily: they appear on the right side of each cell (if not formatted).
How to use COUNT
In Excel, COUNT is an easy function to use. Just enter the range or cell you want the system to check for numbers using the formula =COUNT(value). Note that you may enter multiple values in the Excel COUNT formula this way: =COUNT(value1, value2, value3, value4,…).
COUNT examples
Check these examples to understand how the COUNT function works:
In this example, we want to check how many cells contain numbers. It’s an Excel table we used in the past for the VLOOKUP function (click here to learn more about VLOOKUP).
The formula we use is =COUNT(B2:B5) and the result is 1. Why?
One of the VLOOKUP results is 0 so the COUNT function totalises 1 result. You will notice that only the 0 in cell B3 is on the right of the cell: this means the cell contains a number and other cells don’t. The result of the COUNT function is also on the right of the cell B7 because it’s a number.
In this other example, we search how many cells contain numbers in our Excel worksheet. We want to analyse range C2:C11 and range C29:34; the formula we use is =COUNT(C2:C11,C29:C34).
The first range contains results of IFERROR and SEARCH functions and the other cells contain results of MID and RIGHT formulas. Check our blog to learn about these handy Excel functions!
The result of the COUNT function is 8. But why?
The number of cells with data on their right side is 8. The cells from C29 to C34 contain numbers but in a text format (they appear on the left side of each cell).
How to count cells that are not empty in Excel?
In Microsoft Excel, to know how many cells are not empty use the COUNTA formula. The Excel COUNTA formula is =COUNTA(value1, value2,…)
value1 — it can be a cell reference or a specific range.
value2,… — you can add up to 256 arguments (values) in this formula.
What’s the difference between COUNT and COUNTA?
In Excel, the COUNT function calculates the number of cells containing numerical values while COUNTA counts all cells which are not empty.
Note about the COUNTA function
– The COUNTA function will count all cells which are not empty, which means cells containing empty text (“”) or error values too.
How to use the COUNTA function in Excel
Enter the range of cells or the cell you want the system to check using the formula =COUNTA(value). You can enter multiple values in the COUNTA formula this way: =COUNTA(value1, value2, value3, value4,…).
COUNTA examples
Check these 2 examples to see how COUNTA works in Excel:
In this example, we want to make sure we did not forget entering any values in the cells highlighted in green.
The COUNTA formula we use is =COUNTA(G2:H4,G6:H6) and the result is 8. 8 of the 8 cells analysed contain data.
In this other example, we want to double-check if all cells in column E (5 in total) contain data: we don’t want any empty cell in our Excel table.
The Excel COUNTA formula we use is =COUNTA(E10:E14).
But why is the result 5 if we don’t see anything in cell E12?
In this table, we used the IFNA function where we instructed the system to add an empty text (“”) if there was no value associated, that’s why the system does not consider cell E12 as empty.
To learn more about the IFNA function, check this tutorial.
Now, you know how to check cells with text or numbers using COUNT and COUNTA functions in Excel. You also know the difference between COUNT and COUNTA functions.
As we love sharing our tips and knowledge, here is the Excel formula to count empty cells: COUNTBLANK.
How to count empty cells in Excel?
Use COUNTBLANK to count empty cells in Excel. The Excel COUNTBLANK formula is =COUNTBLANK(range)
Notes
– Cells with empty text (“”) are considered empty and are counted with COUNTBLANK.
– Cells with empty text (“”) are not considered empty with COUNT and COUNTA functions.
Now, you know how to count cells with text, numbers or even empty cells in Excel spreadsheets.
Time to join our Online Excel Course to use Excel like a pro!
