Count Cells in Excel (12 Real-Life Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will go over how you can count cells in Excel for the various needs you may have. Counting Cells is one of the most frequently performed tasks in Excel.

Cells counts are important for various reasons including analyzing the data table, data analysis, and many more. Excel provides some useful functions that you can use to count cells differently.

Master the skill of counting cells, be they blank or non-blank, with texts or numbers, and use this skill effectively in your worksheet.


Download Practice Workbook

You can practice on your own by downloading the free Excel worksheet.


How to Count Cells in Excel

1. Count Cells with Numbers with COUNT Function

The COUNT function returns the count of cells that contain numbers in them. Use the following formula to count cells that contain numbers in the dataset.

=COUNT(D5:D15)

Counting cells with numbers


2. Count Any Cell Data with COUNTA Function

You can use the COUNTA function to count cells that contain any type of data. Use the formula:

=COUNTA(B5:B15)

Count any cell data in Excel


3. Using COUNTBLANK Function to Count Blank Cells

Excel has the dedicated COUNTBLANK function to count blank cells. To count the blank cells, use the following formula:

=COUNTBLANK(D5:D15)

Count blank cells


4. Counting Empty Strings

An empty string, written by inserting:

=""

This will show up both in the COUNTA and COUNTBLANK formulas. This is because the COUNTA function treats this as a text string and the COUNTBLANK function treats this as a blank cell.

Counting empty cells


5. Using COUNTIF to Count Cells Based on Criteria

If you want to count cells based on a criterion, then the COUNTIF function is the one to use. To match a criterion exactly and count cells based on that matched criterion, use the following formula:

=COUNTIF(E5:E15,"Biographical Novel")

Counting based on criterion

We want to match Biographical Novel so we insert “Biographical Novel” in the second argument for the COUNTIF formula.


6. Count Cells with Multiple Criteria Using COUNTIFS Function

If you want to count cells based on multiple criteria, then using the COUNTIFS function is the best practice. We use the following formula to count cells using multiple criteria:

=COUNTIFS(C5:C15,"Charlotte Bronte",D5:D15,">1845")

Counting based on multiple criteria using COUNTIFS function


7. Count Cells with Specific Text

We can use the COUNTIF function to match a string partially at the beginning, in the middle, or at the end and count cells based on that match.

7.1 Partial Match at Beginning

We use the following formula to count the books that are Biographical genre.

=COUNTIF(E5:E15,"Biographical*")

Counting based on partial match at beginning

7.2 Partial Match in the Middle

The following formula returns the number of books that have cal in the middle of the genre.

=COUNTIF(E5:E15,"*cal*")

Counting based on partial match in middle

7.3 Partial Match at End

The following formula returns the number of books that matches the word Novel at the end.

=COUNTIF(E5:E15,"*Novel")

Counting based on partial match at End


8. Count Cells with Duplicate Text in Excel

We can employ the COUNTIF function to count the number of duplicates in a range of cells easily. We want to count the number of books that were published in the year 1847. For this, we use the following formula.

=COUNTIF(D5:D15,$D$5)

Counting duplicate cells with COUNTIF function in Excel


9. Count Cells with Colored Text

Excel’s default features do not include counting cells by the color of the cell’s text. But, we can use the Filter feature along with the SUBTOTAL function to get around this. The years that are before 1860 have Green font text and the years after 1860 have Blue font text.

  • Select any cell in the dataset and go to Data > Sort & Filter Group > Filter to enable filtering.

Enable filter from Sort & Filter group

  • Right-click on the desired cell of the text color that you want to count and go to Filter > Filter by Selected Cell’s Font Color.

Filter by selected cell's font color

  • Now, use the SUBTOTAL function to get the count of the cells based on the text’s font color.
=SUBTOTAL(2,C5:C15)

Counting cells based on text color in Excel

Note: You have to input the whole range, otherwise, the result may be incorrect.

10. Count Cells Using SUMPRODUCT Function

You can use the SUMPRODUCT function to count cells based on one or more than one criterion. To get the number of books by Charles Dickens after 1855, we use the following formula:

=SUMPRODUCT(--(D5:D15="Charles Dickens"),--(C5:C15>=1855)

Counting cells with SUMPRODUCT function in Excel

Formula Breakdown:

SUMPRODUCT(–(D5:D15=”Charles Dickens”),–(C5:C15>=1855)

  • (D5:D15=”Charles Dickens”)

This condition checks if the corresponding values in the range D5:D15 are equal to “Charles Dickens”. This returns an array of TRUE or FALSE values.

  • –(D5:D15=”Charles Dickens”)

The (–) sign converts the TRUE and FALSE values into 1s and 0s.

  • (C5:C15>=1859)

This condition checks if the values in the range C5:C15 are greater than or equal to 1859. It returns an array of TRUE or FALSE values.

  • –(C5:C15>=1859)

The (–) sign converts the TRUE and FALSE values into 1s and 0s.

  • SUMPRODUCT(–(D5:D15=”Charles Dickens”),–(C5:C15>=1855)

This function counts the number of occurrences where both conditions are met.


11. Count Cells from Filtered List

  • We apply a filter to the list by selecting the filter button from the Published year column header and going to Number Filters > Greater Than.

Create custom filter to count cells

  • In the popup window that appears, insert the value in the is greater than box and click on OK.

Insert value to filter with

  • The lists are filtered.

Filter list after applying custom filter

11.1 Applying SUBTOTAL Function

After applying the filter, you can use the SUBTOTAL function to count the cells that are in the filtered list. We use the following formula to count books that are published after the year 1855.

=SUBTOTAL(2,D5:D15)

2 means to perform count in the specified range.

Count cells from filtered list using SUBTOTAL function

11.2 Utilizing AGGREGATE Function

You can also use the AGGREGATE function to achieve the same result. We use the following formula:

=AGGREGATE(2,3,D5:D15)

Count cells from filtered list using AGGREGATE function


12. Count Total Cells in Range

You can easily count total cells in a range by applying the ROWS and COLUMNS functions.

We use the following formula to calculate the total cells in the range.

=ROWS(B5:E15)*COLUMNS(B5:E15)

Counting total cells in range


Things to Remember

  • The COUNT function cannot count non-numerical values and returns 0 as a result.
  • The COUNTA function can count all data types in a range.
  • Make sure to include the full range when calculating the count with the SUBTOTAL function for the filtered list.
  • Make sure a cell is truly blank or empty before counting cells. Otherwise, it may generate incorrect results.

Conclusion

Counting cells in Excel may be necessary for various purposes. This is an important indicator that may be used differently in Excel. Go over the methods described in this article to get a proper grip on how to count cells in Excel. Use the knowledge whenever necessary for better data management and analysis.


Frequently Asked Questions

1. Can I count the blank cells in a range?

A: Use the COUNTBLANK function to count blank cells in a range.

2. Is there any way I can count cells based on specific criteria?

A: Yes, you can use the COUNTIF function when there is a single criterion and the COUNTIFS function when there are multiple criteria.

3. Is there any difference between COUNT and COUNTA functions?

A: The COUNT counts only the cells that contain numbers whereas the COUNTIFS function counts only the non-blank cells in a range.


Count Cells in Excel: Knowledge Hub


<< Go Back to Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Niaz
Maruf Niaz

Md. Maruf Niaz, BSc, Textile Engineering, Bangladesh University of Textiles, has worked with the ExcelDemy project for 11 months. He works as an Excel and VBA Content Developer who provides easy solutions to Excel-related problems and regularly writes fantastic content articles. He has published almost 20 articles in ExcelDemy. As an Excel & VBA Content Developer for ExcelDemy, he offers solutions to complex issues. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo