COUNT Function in Excel (Table of Contents)
COUNT in Excel
As the name itself denotes COUNT means, count something in the range but only numerical values. Anything other than numerical values COUNT will ignore.
Any text or string cannot be counted with COUNT Function in Excel. Rather we can use COUTNA function that can count anything in the given range of cells.
Any text values in excel cannot be summed by SUM function and cannot be counted by COUNT function in excel.
COUNT Formula in Excel
The Formula for the COUNT Function in Excel is as follows:
The Formula of COUNT Function many of value1, value2, value 3.
Value 1: This is the mandatory parameter. This is the first cell or range we are counting.
4.8 (2,156 ratings)
Value 2: This is the second set of cell or range we are looking to count. However, it is not a mandatory argument. Once the first Value 1 is, all other values become optional.
Note: we can give up to 256 values to COUNT function.
The return of the COUNT function is always either zero or greater than zero.
How to Use COUNT Function in Excel?
This COUNT Function is very simple easy to use. Let us now see how to use the COUNT Function in Excel with the help of some examples.
Consider the below data and apply COUNT function to find the total numerical values in the range.
Apply COUNT function.
It returns the total numeric values as 4.
Look how many cells did COUNT function counted.
In the supplied range there are 15 values are there but COUNT function ignored everything and counted only numerical values (red boxes).
COUNT function can count dates also if it is in the correct format. We will see the below example how COUNT works for date values.
Now apply COUNT function.
COUNT range is from A2:A8 and there are totally 7 date values are there. COUNT function returned a result as only 5.
Look at the red color marked cells, those cells date values are not incorrect date format. Even though excel stores dates as serial numbers COUNT function always ignores wrong formatted date values from the calculation.
COUNT function can be used with other functions also. In this example, I will show you how to use COUNT with IF condition.
Below is the attendance data for 7 days. If the employee is present then it is marked as 1 and if the employee is absent then it is marked as A. Total wages for the week is also provided.
There are a couple of ways to calculate the wages. I will explain one by one.
Method #1 –
Calculate the paid days and multiply the paid days by Wages.
It returns the result as :
Using COUNT function, I got the total paid days.
To Find Total Wages below Formula is used:
It returns the result as:
Method #2 –
To check whether an employee is eligible for full pay or not we can use COUNT with IF condition.
=IF (COUNT (C2:I2) =7,”Full Pay”, “Not Full Pay”)
There are 7 working days in the above data. If condition along with the COUNT function checking whether the total count of number is equal to 7 or not.
If the total count of number is 7 then if condition returns Full Pay if not IF condition returns Not Full Pay.
Things to Remember About COUNT Function
- Only numerical values are counted in COUNT function.
- COUNT function ignores empty cells, text and string values, and error values in the array.
- If the COUNT function is applied to an empty range of cells then the result will always be zero.
- If the number is followed by a text, COUNT ignores that value also. For example, =COUNT (“145 Number”) would return the result as 0.
- If the logical values such as TRUE or FALSE supplied to the formula then these logical values will be counted by COUNT function.
- If the same TRUE or FALSE supplied in a range then the result will be zero.
- If you want the count of all the values in the given range then use COUNTA that counts whatever comes in its way.
This has been a guide to COUNT in Excel. Here we discuss the COUNT Formula in Excel and how to use COUNT Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –