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)
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)
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)
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.
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")
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")
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*")
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*")
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")
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)
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.
- 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.
- Now, use the SUBTOTAL function to get the count of the cells based on the text’s font color.
=SUBTOTAL(2,C5:C15)
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)
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.
- In the popup window that appears, insert the value in the is greater than box and click on OK.
- The lists are filtered.
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.
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)
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)
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
