Businesses nowadays store their financial data in an Excel spreadsheet, from simple monthly budgets to profit and loss statements, from invoices to bank loan amortization. The COUNT function in Excel as its name suggest counts the number of cells, it’s pretty straightforward but it also analyzes your data with its many functions and formulas. In financial modeling, you can use it for calculating a full-year forecast by counting the number of months in your data, you can use it for determining total headcount costs for a department’s budgeting purposes or even counting the number of quantity sold in the past years to identify any trends.

Learning how to use COUNT Function in Excel is equally helpful not only for financial modelers and analysts but also for small business owners. COUNT functions in Excel are useful for small business owners from counting inventory to counting the shift hours of employees.

In this article, we will run through the COUNT functions in Excel categorized under Statistical Function in Excel.

1. COUNT Function in Excel to count cells with numbers.
2. COUNTA Function in Excel to count non-blank cells (numbers but also words or other formats)
3. COUNTBLANK Function in Excel to count blank cells
4. COUNTIF Function in Excel to count cells with specific text (based on a single condition)
5. COUNTIFS Function Excel to count cells with specific texts (based on multiple conditions)

No. 1 COUNT Function In Excel To Count Cells With Numbers

The COUNT function in Excel is used in Excel to count cells with numbers. The COUNT function won’t work if you need to count how many cells show “Canada” or “Australia” (for this you will have to use the COUNTA Function which we will discuss later on below). The COUNT function in Excel results in an integer number, the value can either be zero and non-zero.

The COUNT Formula in Excel

Where:

value1 is a required argument and represents cell references or ranges where you want Excel to count cells with numbers

[value2], … The bracket means [] this argument is optional

How to Use COUNT Function in Excel Example

A hypothetical Sales Data list for the month of February is as follows:

Using the COUNT function in Excel to count the number of cells under Dollar Sales column, the COUNT formula =COUNT(B3:B18) gives us a total count of 16.

Assume, we will add optional arguments [value2], [value3], [value4],…represented by the numbers 1,2,3. The COUNT formula =COUNT(B3:B18,1,2,3) gives us a higher total count of 19. Why?

COUNT function in Excel not only count cells with numbers but also counts any number contained within the formula itself. Since [[value2], [value3], [value4], … contains the numbers 1,2,3, the COUNT function in Excel included all these numbers to its total count.

No. 2 COUNTA Function in Excel Count Non-Blank Cells

Non-blank cells are cells that contain values such as numbers, spaces, text, and errors (#VALUE!, #N/A, etc.). Assume our Sales Data list is incomplete and a few cells contained the text “Undelivered”. So now – opposite to the COUNT Function in Excel – it is possible to count how many times cells are filled with certain words such as “Canada”, “Australia” or in below example “Undelivered”.

Using the COUNT function in Excel, the total count result is 12 (vs. 16). An important thing to remember on how to use COUNT Function in Excel is it ignores cells containing anything but a number.  If a column contains a mix of numbers and values (space, error, text, etc.) these will not be counted and Excel count cells with numbers only.

So, how to use COUNT function in Excel count non-blank cells or cells with values?

You can use Excel to count non-blank cells using the COUNTA function.

The COUNTA Formula in Excel

Where:

value1 is a required argument and represents cell references or ranges where you want Excel to count non-blank cells

[value2], … The bracket [] means this argument is optional

It is important to keep in mind that COUNTA function counts cells containing any type of data listed below but keep in mind it always ignores empty or blank cells.

1. Numbers (10,200,.05, 1.10)
2. Dates and times (9:00,14/2/2018)
3. Text (“John”, “April”)
4. Error values like #N/A, #VALUE, etc.
5. Empty text strings (“”) or spaces

How Excel Count Non-Blank Cells Example

Using the same Sales Data list we can count the number of cells under the Dollar Sales column with formula =COUNTA(B3:B18) which gives us the original total count of 16.

No. 3 COUNTBLANK Function in Excel Count Blank Cells

Depending on the financial data you are working with, there might be times you need Excel to count blank cells.  A blank cell is equal to an empty cell, so cells that contain zero (0), errors (#VALUE!, #N/A, etc.), and spaces will not be counted.

The COUNT function in Excel to count blank cells only is aptly named the COUNTBLANK function.

The COUNTBLANK Formula in Excel

How Excel Count Blank Cells Example

Assume our Sales Data list contains missing information and so cells are left blank. Using the COUNT function in Excel it only gives us a total of 12. But we know that we have a total of 16 for the month.

Using Excel to count blank cells, the COUNTBLANK formula =COUNTBLANK(B3:B18) results in a total count of 4.

No. 4 COUNTIF Function In Excel Count Cells With Specific Text

So far we have discussed different ways of how to use COUNT functions in Excel to count numbers, values, and blank cells. Let’s move on to using Excel to count cells with specific text.

For Excel to count cells with specific text, the specified text now serves as a criterion that must match with the cells to be counted. Bear in mind the text criterion must be enclosed in double-quotes “  “ otherwise Excel will not read it as a text.

In this case, the COUNT function in Excel only works IF it meets the specific text criterion- thus aptly named the COUNTIF function.

The COUNTIF Formula in Excel

Where:

• Range. The range of cells will be assessed against your criteria. The criteria must be satisfied first before cells will be counted. Otherwise, it will be ignored.
• Criteria. The defined condition where each of the cells in a given range is assessed.

Assume our chosen text is “Undelivered”, with the COUNTIF formula =COUNTIF(B3:B18,”undelivered”) this gives us the total count of 4. The Range (B3:B18) represents the cells that are assessed against Criteria “Undelivered”. Do take note, Excel is not case sensitive so it’s correct either way you typed “undelivered” vs. Undelivered”.

Learn more about how Excel count cells with specific text and with different types of criteria by reading the article: Using COUNTIF Function in Excel.

No. 5 COUNTIFS Function Excel Count Cells With Specific Texts (Multiple Criteria)

It is very useful to understand another form of COUNT function in Excel if you need to expand the count to meet multiple conditions or criteria. Due to the multiple conditions involved, this is called the COUNTIFS function in Excel. A typical example would be, to count cells containing a specific text No.1 but exclude cells that are equal to specific text No. 2

The COUNTIFS Formula in Excel

Where:

• Criteria_Range1. The range of cells that will be tested against the criteria and counted if the criteria are satisfied.
• Criteria1. The defined condition or parameter each of the cells in a given range is tested against.
• Criteria_Range2. The second (optional) range to evaluate.
• Criteria2. The second (optional) criteria to use on range2.

Expanding on our prior example, assume we added a new column called “Sales Manager”.

Aside from instructing Excel count cells with specific text “Undelivered”, we can add a second specific text, a Sales Manager named “Desmond”. So, we now have two criteria:

Criteria1 “Undeliverable”
Criteria2 “Desmond”

Using Excel count cells with specific texts, Criteria1 and Criteria2, the COUNTIFS formula =COUNTIFS(C3:C18,”Undelivered”,B3:B18,”Desmond”) results in a total count of 2.

Learn more about this form of COUNT Function in Excel to count cells with specific texts and other types of criteria by reading this article: How to Use COUNTIFS Function in Excel.

Key Points On The 5 Ways of How To Use Count Function In Excel