A B
1 0 =A1, which is blank
2 0 =A2, which is blank
3 0 =A3, which is blank


4 P P =A4
4 =subtotal(103, B1: B4)

In the above, cells B1 to B4 are referenced to blank cells A1 to A4, respectively.

However, instead of displaying 'blank' in B1 to B3, they display '0'. Because of that, the function 'Subtotal(103,B1:B4)', which counts B1 to B4, returns 4, instead of 1.

I tried going to Options/Advanced and unchecked 'show a zero in cells that have zero value', which makes cell B1 to B3 blank, but the 'Subtotal(103,B1:B4)' still returns 4.

I want that formula to return 1, not counting the blank cells. Is there a way to do that?

Hi

3 options:
1. Refer your formula to column A =SUBTOTAL(103,A1:A4)
2. That's only possible if cells in column A are either empty or contain text, then you can count twice and substract results of numbers form the total:
=SUBTOTAL(103,B1:B4)-SUBTOTAL(102,B1:B4)
3. Modify your formulas in column B to =IF(A1="",#N/A,A1) (copying to the end of your list)
Now you can use =AGGREGATE(3,3,B1:B4) to count.