606
If you want to count cells with content in Excel, you don’t have to do it laboriously by hand. You can use simple formulas to quickly find out the number of filled cells.
How to count the cells with content in Excel
If you want to count the number of cells with any content (both numbers and letters) in Excel, choose the following function:
- Select an empty cell in the table where the result is to appear later.
- Enter this formula: =NUMBER2(value1:value2)
- Adjust the formula for the range you want to count. For example, if you want to count the cells with contents from A1 to A50, enter =ANZAHL2(A1:A50).
Count the number of cells with text
If you only want to count the number of cells that contain text (and no numbers), use the following formula:
- Select an empty cell for the result.
- Enter this formula: =NUMBER IF(value1:value2 ; “*”)
- Adjust value1 and value2 accordingly: =NUMBERIF(A1:A50 ; “*”) counts all cells with text in the range from A1 to A50.
Count cells with digits
It also works the other way round. With the following formula you only count the cells with digits (and none with letters):
- Mark an empty cell in the table.
- Enter this formula: =NUMBER(value1:value2)
- Example: =ANZAHL(A1:A50) counts all cells containing digits in the range A1 to A50.
Count cells without content
If you want to count the cells that have no content, this formula will get you there:
- Mark an empty cell in the table, which should not be counted itself.
- Enter the formula: =NUMBER EMPTY CELLS(Value1:Value2)
- Adapt the formula to the range of your table. If you want to count the empty cells from A1 to A50, enter this: =NUMBER EMPTY CELLS(A1:A50)
