In almost all business environments these days, being proficient in essential functions is crucial, and knowing how to count in Excel is undoubtedly one of those foundational skills.
Excel offers a variety of methods to count cells, whether they belong to a specific list, range, or broader dataset. While some techniques shine in particular scenarios depending on the type of data at hand, others serve more general purposes. Dive into this detailed guide to uncover the diverse ways of counting in Excel.
Counting in Excel
There are four key functions for counting in Excel and worthwhile making any Excel users knowledge base. The four functions to know are:
We’ll look at each of these Excel counting functions in more detail to understand when best to use.
COUNT
The COUNT function in Excel can be used to count the number of cells within a specified data range that contain values. By Values that means numerical values and not text or strings. The COUNT function will ignore any text values in the specified range and it will also ignore any blank or empty cells in the range.
For example if you wanted to count the number of values in cells B2 to B11 then you would use:
- = COUNT(B2:B11)
That would produce an answer of 10 because there are 10 values in the specified range:
However if you change a couple of the values in the range, making cell C4 blank and cell C8 equal to ABC you will notice the COUNT function does not produce an error, rather it Ignores those two values and produces a result of 8 values in the specified range:
COUNTA
The COUNTA function in Excel can be used to count the number of cells within a specified data range that contain values or text, basically this function will count any cell in the range that is not empty.
If we refer back to our example and change the formula in cell B12 to:
- =COUNTA(B2:B11)
It will produce a result of 9 as there are 9 non-empty cells in the specified data range:
That covers counting values and counting non-empty cells, but what about if you want to count only specific cells?
COUNTIF
The COUNTIF function in Excel can be used to count the number of cells within a specified data range that meet a certain criteria. This is a highly useful function as you can use it to just count specific values, i.e. those over 5 and you can even adopt a little trick to count all cells in a range, regardless of whether they are blank, text or values.
Falling back to the current example if you want to count all cells in the range that are over 5 the formula in cell B12 to use is:
- =COUNTIF(B2:B11,”>5″)
Breaking the formula down into more detail the COUNTIF function requires two inputs:
- The Data Range: In this example the specified data range is cells B2:B11.
- The Criteria to Apply when Counting: After specifying the range a comma is used as a separator and then your criteria is written in quotation marks so in this case because we want only values greater than 5 the criteria is “>5“.
In action this will return a result of 4 in our example, this is because out of the specified data range of B2:B11 only the values 6,8,9 and 10 meet the criteria of being greater than 5:
You can also use the COUNTIF function to count specific text strings, this can come in useful when performing various analytical tasks in Excel.
If in the example we wanted to count how many cells contain the string “ABC” then the formula to use in cell B12 is:
- =COUNTIF(B2:B11, “ABC”)
There is only one cell with the value “ABC” in the range:
One final trick with the COUNTIF function in Excel is that you can use it to count all cells in a range, regardless of whether they are values, text, empty or not. To do this use the formula below in cell B12:
- =COUNTIF(B2:B11,”<>”””)
This formula counts all 10 cells in the range:
COUNTBLANK
The last Excel counting function to introduce you to is COUNTBLANK and as you have probably guessed this can be used to count the number of blank or empty cells in a specified range.
You might question why this function is useful but it can be invaluable for performing data analysis and quality checks on data sets. Sometimes in Excel data analysis it is very important to understand what data is missing and if a record is supposed to have a value but is empty then the COUNTBLANK function can be used to identify those problems.
To use the COUNTBLANK function the formula is very simple. In cell B12 enter:
- =COUNTBLANK(B2:B11)
With the example the result is 1. This is because there are only 1 blank/empty cells in the specified data range:
Summary
In summary, mastering the art of counting in Excel through its diverse functions – COUNT, COUNTA, COUNTIF, and COUNTBLANK – equips you with the necessary skills to effectively manage and analyse your data.
Whether you’re counting numerical values, non-empty cells, specific criteria, or even blank cells, each function serves a unique purpose and enhances your data handling capabilities. Remember, proficiency in these functions is not just about executing them correctly, but also understanding the context in which each is most effective.
- Use =COUNT for values/numbers
- Use =COUNTA when your data range contains values and text and you want to include both types in the count.
- Use =COUNTIF if you have a criteria to specify, i.e. only values greater than 5 or values that equal “ABC”.
- Use =COUNTBLANK for data quality checks and to find out how many blanks or empty cells are within a range of data.
I hope that proves useful to you in your quest to become a better Excel user. Excel training that even Count von Count himself would be proud of! 🙂
Keep Excelling,
Having learned how to count values, texts, and blanks in Excel, your next step is to delve into calculating the median, a vital statistical function. Our guide on How to Calculate the Median Formula in Excel will help you understand this concept, which offers a more accurate reflection of your dataset, particularly when dealing with outliers. Enhance your data analysis skills even further, what are you waiting for?
