Count Functions in Excel – The Complete Guide

By /
USING COUNT FUNCTIONS IN eXCEL

This tutorial is all about counting different values/contents in a cell in Microsoft Excel. we will learn in-depth about different count functions in Excel such as COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.

Steps to Use the Count Functions in Excel

Let’s get started with this simple and easy guide to using the count functions to count values in cells in Excel, step-by-step.

1. Counting numeric and textual data in Excel

To count the number of numeric and textual data within cells, use the COUNTA formula by following the steps below.

Let us take a dataset with some textual and numeric data in its rows.

Count Functions in Excel
  • Type =COUNTA( in a blank cell.
  • Select the cells containing textual and numeric data.
  • Close the parenthesis and press ENTER.
Count Functions in Excel

Notice that we have excluded Particulars from the selection as it is a heading.

You can see that the COUNTA function has now counted the number of cells with textual and numeric data for you. It depicts we have 4 cells with some text and numeric data in them, which is true in this case.

counta counted

2. Count functions for numeric data only

To count the number of numeric data within cells, use the COUNT functions by following the steps.

Let us take a dataset with some numeric data in its rows.

num data
  • Type =COUNT( in a blank cell.
  • Select the cells containing numeric data.
  • Close the parenthesis and press ENTER.
count

You can see that the COUNT formula has now calculated the number of cells with only numeric data in them and has not calculated the blank cells in the selection. It shows the number 3 meaning there are three cells with only numeric data in them.

count counted

3. Counting blank cells in Excel

To count the number of blank cells, use the COUNTBLANK formula by following the steps.

Let us take a dataset with some numeric data and a blank cell in its rows.

blank data 1
  • Type =COUNTBLANK( in a blank cell.
  • Select the cells you want to calculate blank cells for.
  • Close the parenthesis and press ENTER.
countblank

You can see that the COUNTBLANK formula has now calculated the number of blank cells within the selected range. It shows the number 2 meaning there are two blank cells within the selected range.

countblank counted

Using the COUNTIF formula in Excel

The COUNTIF formula in Excel can be used to count the number of custom values that you prefer within a selected range. Let’s learn how.

We will take an example of an attendance sheet below to learn to use this formula.

attendance sheet
  • In this sheet, “P” denotes present and “A” denotes absent for a student.
  • The goal is to calculate the total number of Ps and the number of As for every student in the sheet to determine the total number of days present and total number of days absent.
  • This can be achieved with the help of the COUNTIF formula in Excel. Let’s begin using the formula in the next steps.
  • Type =COUNTIF( under the Present column.
  • Select the ranges marked with P and A for the first student only.
  • Put a comma to move to the Criteria argument.
  • Type “P” and close brackets.
countif
  • Press ENTER to apply the formula.
  • Repeat the same under the Absent column of the first student by replacing the criteria with “A”.
countif counted

You can see that the COUNTIF formula has been applied for the first student correctly. We can say that the student was present on 10 days and absent for 5 days on a total of 15 working days.

  • To calculate the number of present and absent days for other students, simply double-click the bottom right corner of the first cell until the cursor turns into a black plus sign to auto-fill the same formula to adjacent cells.

Recommended read: How to Use AutoFill in Excel?

autofill countif

You can see that all the cells of all students have been automatically filled.

Using the COUNTIFS formula in Excel

The COUNTIFS formula in Excel is more specific in nature and can be used to count the number of custom values that you prefer by providing multiple criteria within a selected range.

For example, you can specifically find out the total number of boys or girls in a class who secured higher than 70% in their examinations.

Let’s put this example into practice by applying the COUNTIFS formula. We take a sample mark list of students along with their genders in a column.

countifs sample data

Now, let us begin sorting all girls who scored above 60% by following the steps below.

  • Type =COUNTIFS( in a blank cell.
  • Select the Girl/Boy range in the dataset when asked for Criteria_range1.
  • Put a comma to move to the Criteria1 argument.
  •  Select a cell named Girl in that range.
  • Put a comma and move to the Criteria_range2 argument.
  • Select the Percentage column range in this argument.
  • Put a comma and type “>60”.
  • Close the brackets and hit ENTER on your keyboard.

This is how your formula should look like before pressing ENTER.

countifs
  • Once you’ve pressed ENTER, you should then see the number 3 which simply denotes that there 3 girls in the range who have scored above 60% in examinations.
  • Repeat the same steps by selecting a cell named Boy at the Criteria1 argument to find out the number of boys who have scored above 60% in exams.
countifs calculated

Conclusion

This was all about counting in Excel and the multiple count functions in the application that can be used to count various types of data or values. If you have any doubts regarding these COUNT functions, let us know in the comments below and we will surely help you out!

References: Microsoft