Excel formula "counta" return 1 on a range of empty cells

1
votes

I got a file I am working on that contains various sheets and I am trying to get a "COUNTA" formula to update the values of the not empty cells of a certain range.

The problem is the formula keeps returning "1".

I got "=IFERROR(COUNTA('A-Servers'!B2:B31;);"0")" on Sheet B that reffers to Sheet A.

To be absolutely sure, I went to Sheet A and got the "LEN" for the cell range I wanna check with "=LEN(B2)" up to B31 and go "0" all the way down.

The only thing I assume I am not sure and might be the cause of this, is that the range got the cells merged on every 2 cells, so B2 and B3 are merged as well as B4 with B5 and so on.

BTW I am using excel 2013

Could you guys help me figure out why the CountA formula keeps returning the count of 1 cell not empty?

2
countexcel-formulaexcel-2013

2 Answers

0
votes

COUNTA also counts cells with the empty string (""), even though they have a length of zero. Merging does not impact this. So, select the range and press delete, and you should be brought back to zero.

(FYI - formulas also make a cell non-empty, since they must have an output. So a formula that appears to have no output will instead output the empty string.)

0
votes

What worked for me was adding a "-1" on the end of the formula like this: =IFERROR(COUNTA('A-Servers'!B692:B721;)-1;"0") and it works fine.