In data analysis, analysts usually work with a lot of data. They usually work with multiple data sets with thousands of rows, columns, and values.
The type of values also differs from each other. Values can be numbers, texts, dates, etc.
Analysts frequently need to count these values for various reasons. Perhaps, they want to find out how many orders have been placed, how many customers have placed those orders, etc.
Scouring through thousands of data points is not efficient nor feasible. It is almost impossible for a human being, however for excel, it is a matter of seconds.
You can count occurrences in excel very easily and almost immediately.
So, let’s learn how to count occurrences in excel.
How to Count Occurrences in Excel?
The formula that is used to count occurrences is known as COUNT.
There are quite a few variations of that formula, let’s see one by one how to count occurrences in excel by using the COUNT formula.
#1 The COUNT formula to count numbers in a range
The table in the picture shows individuals from different states and their salary, birth date, gender, and the number of children they have.
The COUNT formula is a very basic and simple formula in Excel that is used to count numbers in a range.
So, if you apply the formula in a range that contains texts it won’t work, and it will show a result of 0.
So, let’s see how to use the COUNT formula in excel.
Type the following formula in excel:
=COUNT(D2:D33)
Notice that, I have applied the formula in the D column where the values are numbers. If you press enter, you will see that there are 30 values in the Children column.
#2 The COUNTA formula to count texts in a range
The COUNTA formula is also a very basic and simple formula in Excel that is used to count texts in a range.
So, if you apply the formula in a range that contains numbers it won’t work, and it will show a result of 0.
So, let’s see how to use the COUNTA formula in excel.
Type the following formula in excel:
=COUNTA(C2:C33)
Notice that, I have applied the formula in the C column where the values are texts. If you press enter, you will see that there are 30 values in the State column.
#3 The COUNTBLANK formula to count blank cells in a range
You can also count blank cells in a range in excel. There is a formula for that, COUNTBLANK.
You can apply this formula in a range that contains any value format. It will count the blank cells in that range.
So, let’s see how to use the COUNTBLANK formula in excel.
Type the following formula in excel:
=COUNTBLANK(A2:A33)
If you press enter, you will see that there are 2 blank cells. You can apply the formula in any range you like, you will see the same result of 2 blanks.
Conclusion
You can also use conditions to count occurrences. For example- you can count how many individuals earn more than a certain amount, how many females earn a certain amount, etc.
But let’s keep this for another blog.
For now, you have learned how to count occurrences in excel.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.