Excel users often need to count values stored in a data set to fulfill their requirements. When we say value, the first thing that comes into mind what type of value? Because a value could be a number, text, date, time, or logical values, this distinction matters. Excel provides special functions to count cells containing values that meet certain conditions, so Excel’s COUNT function is one of those special functions.
How to use the COUNT function in Excel
The Excel COUNT function counts cells that contain numbers as well as the arguments that contain numbers. It is categorized as Statistical function and can handle up to 255 values or value arguments.
Basic formula using COUNT
Syntax of COUNT function is;
=COUNT (value1, [value2], …)
There are some points to remember while using COUNT function.
- A value argument in COUNT function can be an item or constant (Number or Text), cell reference or a range.
- Values can be entered as a mix of constants, cell references, and ranges.
- As date and time values are stored as a serial number in Excel, so these values are counted as a number.
- If a value argument in Excel COUNT function is a cell reference or range, then only numbers, dates, and time values are counted as numbers, ignoring all other values.
- But if you directly enter logical values (TRUE and FALSE) and text representation of numbers, like “5”, as value arguments in COUNT function, then these values are considered as numbers and counted in COUNT function.
- Logical values (TRUE and FALSE) and text representation of a number (“5”) entered as cell references or ranges in Excel COUNT Function are not considered as numbers and are not counted.
- Empty cells are not counted.
For example, you want to count the numeric values from a mix of different type of values, so the Excel COUNT function can do that easily for you. You can supply these values directly or cell references or range in COUNT function. But based on the above points COUNT function might give you different results as this function behaves differently for certain values when they are entered directly or a cell reference or range. In this article, you need to learn how to differentiate between such values.
So, you can count the numeric values as a mix of values when entered directly in COUNT function as follows;
In this case COUNT function gives total count as 6 because it counts numbers, a text representation of numbers, logical values, dates, and time values as numbers, ignoring only text values.
But when you supply cell reference(s) or range(s) in Excel COUNT Function then only numbers, dates and time values are counted as numbers, ignoring text values, empty cells, a text representation of numbers, and logical values.
You can also count the numbers entered in various ranges using Excel COUNT function. Suppose you want to count the numbers stored in two or more different ranges, so you can do it easily by supplying two or more ranges as value arguments to come up with a total count of numbers.
In this case, you have various types of values in two different ranges, having numbers, texts, dates, errors, and empty cells and you need to count the numbers in these two ranges. You need to supply these two ranges as values in COUNT function as follows;
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.