Ask a Question

Excel: Counting, if not empty - that's how it works

+3 votes
105 views
in Office by (242k points)
reopened
Excel: Counting, if not empty - that's how it works

1 Answer

+4 votes
by (1.6m points)
 
Best answer

How to count all cells in Excel that are not blank
1st step:
2nd step:
3rd step:
quick start Guide

Do you want to ignore the blank cells when counting cells in Excel? We show which function works..

image image

Excel functions make your life easier. Would you like to know how many times a particular item has been sold? The COUNTIF function offers exactly this range of functions. But there is also a simplified function in Excel with which you only read out the cells with values ​​that are not empty. You can find out which one is in the following article.

How to count all cells in Excel that are not blank

It doesn't always have to be the slightly more complex COUNTIF function. With the "NUMBER2" function, you can conveniently count all cells that are not empty. It doesn't matter whether the cells contain text or numbers. Follow our step-by-step instructions or take a look at the brief instructions .

1st step:

image
Open your Excel document. In our example there is a table with sellers whose purchase amounts are only partially known. We now want to find out how many cells contain values. Of course, this also works for much larger tables. As an example, we choose a clear table in Excel for understanding.

2nd step:

image
Click on an empty cell in Excel in which the result should then be displayed. Now enter the following form: " = ANZAHL2 (range of values) ". In our example, the range of values ​​extends from B2 to B7 and is noted as B2: B7 in the formula. About [Enter] to confirm the formula.

3rd step:

image
It is best to give one of the adjacent cells a unique name such as "Number" for the cells that are not empty. In our example, 4 cells in the Excel table are not empty. Tip: You can also count the cells in more than one range. For example, if you want to count B1 to E6 and then B8 to E14, enter "= ANZAHL2 (B1: E6; B8: E14)".

quick start Guide

  1. Open an Excel spreadsheet with missing, blank cells.
  2. Specify the range of cells you want to count - for example B2: B7.
  3. Then select the cell in which the result should appear.
  4. As a last step, enter the following formula in the formula bar above: " = NUMBER2 (B2: B7) ". Adjust the cell range accordingly. Confirm the formula with [Enter] .

...