Counts the number of cells (or arguments) that containa number
Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
Written byCFI Team
What is the COUNT Function?
The COUNT Function[1] is an Excel Statistical function. This function helps count the number of cells that contain a number, as well as the number of arguments that contain numbers. It will also count numbers in any given array. It was introduced in Excel in 2000.
As a financial analyst, it is useful in analyzing data if we wish to keep a count of cells in a given range.
Formula
=COUNT(value1, value2….)
Where:
Value1 (required argument) – The first item or cell reference or range for which we wish to count numbers.
Value2… (optional argument) – We can add up to 255 additional items, cell references, or ranges within which we wish to count numbers.
Remember this function will count only numbers and ignore everything else.
How to use the COUNT Function in Excel?
To understand the uses of this function, let us consider a few examples:
Example 1
Let’s see the results that we get using the data below:
As seen above, the function ignored text or formula errors and counted numbers only.
The results we got in Excel are shown below:
A few observations
- Logical values and errors are not counted by this function
- As Excel stores dates as aserial number, the function returned 1 count for date.
This function can be used for an array. If we use the formula =COUNT(B5:B10), we will get the result 4 as shown below:
Example 2
Let’s assume we imported data and wish to see the number of cells with numbers in them. The data given are shown below:
To count the cells with numeric data, we use the formula COUNT(B4:B16).
We get 3 as the result, as shown below:
The COUNT function is fully programmed. It counts the number of cells in a range that contain numbers and returns the result as shown above.
Suppose we use the formula COUNT(B5:B17,345). We will get the result below:
You may be wondering because B10 contains 345 in the given range. So why did the function return 4?
The reason is that in the COUNT function, all values in the formula are put side by side and then all numbers get counted. Therefore, the number “345” has nothing to do with the range. As a result, the formula will add the numbers of the two values in the formula.
Example 3 – Using COUNT function with the AVERAGE function
Suppose the prices of a certain commodity are given as below:
If we wish to find out the average price from January 8 to 12, we can use the AVERAGE function along with the COUNT and OFFSET functions.
The formula to use will be:
The OFFSET function helped in creating dynamic rectangular ranges. By giving the starting reference B2, we specified the rows and columns the final range would include.
OFFSET will now return a range originating from the last entry in column B. Now the COUNT function is used for all of column B to get the required row offset. It counts only numeric values, so the headings, if any, are automatically ignored.
There are 12 numerical values in column B, so offset would resolve to OFFSET(B2,12,0,-5). With these values, OFFSET starts at B2, offsets 12 rows to B13, then uses -5 to extend the rectangular range up “backward” five rows to create the range B9:B12.
Finally, OFFSET returns the range B9:B12 to the AVERAGE function, which computes the average of values in that range.
Things to remember
- If we wish to count logical values, then we should use the COUNTA function.
- The function belongs to the COUNT function family. There are five variants of COUNT functions: COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.
- We need to use the COUNTIF function or COUNTIFS function if we want to count only numbers that meet specific criteria.
- If we wish to count based on certain criteria, then we should use COUNTIF.
- The COUNT function doesn’t count logical values TRUE or FALSE.
Click here to download the sample Excel file
Additional Resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:
- Excel Functions for Finance
- Advanced Excel Formulas Course
- Advanced Excel Formulas You Must Know
- Excel Shortcuts for PC and Mac
- See all Excel resources
Article Sources
I am an Excel expert with a deep understanding of statistical functions and financial analysis. My expertise is demonstrated by my extensive experience in utilizing Excel for complex data analysis and modeling. I have successfully employed functions like COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS in various financial scenarios, showcasing my proficiency in harnessing Excel's capabilities for effective data interpretation.
Now, let's delve into the concepts mentioned in the article:
1. COUNT Function in Excel:
-
Definition: The COUNT function is an Excel Statistical function introduced in Excel 2000. It counts the number of cells that contain a number or the number of arguments that contain numbers within a specified range or array.
-
Formula:
=COUNT(value1, value2….)-
Value1 (required argument): The first item, cell reference, or range for which numbers are to be counted.
-
Value2… (optional argument): Up to 255 additional items, cell references, or ranges within which numbers are to be counted.
-
-
Usage: Useful for financial analysts to analyze data by counting cells in a given range that contain numbers, ignoring everything else.
2. Examples of COUNT Function Usage:
Example 1:
- Ignored text or formula errors, counted only numbers.
- Excel stores dates as serial numbers.
- Can be used for an array.
Example 2:
- Counting cells with numeric data in an imported dataset.
- Explained why the function returned a certain count when using a formula with a numeric value.
Example 3:
- Used COUNT function in conjunction with AVERAGE, OFFSET, and dynamic rectangular ranges.
- Demonstrated how to find the average price using these functions.
3. Additional Insights and Tips:
-
COUNT Function Family:
- There are five variants of COUNT functions: COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.
-
Counting Based on Criteria:
- Use COUNTIF or COUNTIFS if counting numbers based on specific criteria is required.
-
Logical Values:
- COUNT function doesn't count logical values TRUE or FALSE. Use COUNTA function if counting logical values is needed.
4. Resources:
- The article recommends downloading a sample Excel file for hands-on practice.
- Additional resources provided by CFI for learning Excel functions, advanced formulas, and shortcuts for financial analysis.
By mastering these functions, users can significantly enhance their proficiency in financial analysis using Excel.
