Tanjima Hossain
If you are looking for some of the easiest ways to count if a cell contains number in Excel then you will find this article worthy of it. Sometimes different texts and numbers and other types of variables get mixed up in a column. In this article, I will explain step-by-step procedures to count if cell contains number in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.
In order to count if a cell contains a number, we can use the COUNT, COUNTIF, SUBTOTAL, COUNTIFS, SUBTOTAL, and ISNUMBER functions.
Download Workbook
Download this practice workbook to exercise while you are reading this article.
5 Smart Ways to Count If Cell Contains Number
To make it more simple to understand, I have used the following table where I have arranged some dresses in the Product, Size, and Price columns. I have both text and number variables in the Size column. Here, I would like to count the number of cells based on the numbers in this column.
1. Use COUNT Function to Count Cells with Number
You will be able to count cells containing numbers by using the COUNT function. In this case, I will use the Size column to count the number of cells containing numbers.
Steps:
- Select a cell (i.e. D15) to have output.
- Now, insert the following formula that cell to count cells containing numbers.
=COUNT(C5:C13)- Here, C5:C13 is the range of values
Related Content: Excel Formula to Count Cells with Text (Download Free Workbook)
2. Apply COUNTIF Function to Count Cells with Number
You will be able to count cells containing numbers by using the COUNTIF function. For more details, go through the below section.
Steps:
- Type the following formula in your preferred cell and press ENTER to count cells with number.
=COUNTIF(C5:C13,"<>*")- Here, C5:C13 is the range of values and before the wildcard, <> is used which means Not Equal to any texts.
Related Content: Count Cells That Are Not Blank in Excel (6 Useful Methods)
3. Use SUBTOTAL function to Count Cells with Number
Another simple yet gorgeous way to count cells with number is to use the SUBTOTAL function. Let’s see it in detail.
Steps:
- Pick a cell for the desired output and apply the following formula for counting cells with number.
=SUBTOTAL(102,C5:C13)- Here, 102 is used for using the COUNT function amd C5:C13 is the range of values.
Read More: Count Cells that Contain Specific Text in Excel
4. Apply COUNTIFS Function to Count Cells with Number
In order to count cells with numbers including certain criteria, we can use the COUNTIFS function. It will satisfy all of our conditions.
Steps:
- Apply the following formula to have the total number of cells having number after fulfilling the criteria.
=COUNTIFS(B5:B13,"*Shoe*",D5:D13,">1500")- Here, B5:B13 is the first criteria range and Shoe is the first criteria. Moreover, D5:D13 is the second criteria range and “>1500” is the second criteria.
Read More: Count Filled Cells in Excel Using VBA (7 Methods)
Similar Readings
- How to Count Odd and Even Numbers in Excel (3 Easy Ways)
- Count Only Visible Cells in Excel (5 Tricks)
- How to Count Filled Cells in Excel (5 Quick Ways)
- Count Blank Cells with Condition in Excel (4 Easy Methods)
- How to Count Filtered Rows with Criteria in Excel (5 Easy Ways)
5. Combine SUMPRODUCT and ISNUMBER Functions to Count Cells with Number
You will be able to count cells containing numbers by using the SUMPRODUCT function. But it will be assisted with the ISNUMBER function. In this case, I will use the Size column to count the number of cells containing numbers.
Steps:
- Select a cell (i.e. D15) to have output.
- Now, insert the following formula that cell to count cells containing numbers.
=SUMPRODUCT((--ISNUMBER(C5:C13)))- Here, C5:C13 is the range of the ISNUMBER function.
⧪ Formula Breakdown ⧪
–ISNUMBER(C5:C13) —> checks whether the values in those cells are text or number.
Output: {0;0;1;0;0;1;0;0;1}
SUMPRODUCT((–ISNUMBER(C5:C13)))
SUMPRODUCT(({0;0;1;0;0;1;0;0;1})) —> returns the summation of cells containing number.
Output: 3
Read More: How to Count Non Blank Cells with Condition in Excel (6 Methods)
Conclusion
In this article, I have tried to cover 5 of the simplest ways to count if cell contains number in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.
Related Articles
- How to Count Blank Cells in Excel (5 Ways)
- Count Empty Cells in Excel (4 Ways)
- How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)
- Excel Formula to Count Cells with Text (All Criteria Included)
- How to Count Number of Cells with Dates in Excel (6 Ways)
- Excel Count Number of Cells in Range (6 Easy Ways)
- How to Count Cells with Specific Value in Excel (5 Easy Methods)
