Subtotal with function 103 to ignore blanks

[abdulhaque]

Hi all,

I have the formula (Table1[Flat] is Column D)

=SUBTOTAL(103,Table1[Flat])

to count non-blank cells in a column. However, it is counting blank cells too. Every cell in the column including the blank ones contain the following formula

=IF(ISBLANK(B3), "", IF(AND(B3 >= DATE(2016,3,3),B3 <= DATE(2016,6,12)), "No", "Yes"))

Hence the blank cells contain "". The column is part of a table. I've tried to minus the blank cells from the SUBTOTAL after adding the blank cells of the table column with the COUNTBLANK function, but that doesn't hold when using filters. Any help appreciated.

Thanks

 

[pgc01]

Hi

"Subtotal(103,..." means it will count all cells that are not empty. Since all your cells have formulas there are no empty cells and all cells are counted.

Try:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(Table1[Flat],ROW(Table1[Flat])-MIN(ROW(Table1[Flat])),0,1,1)),--(Table1[Flat]<>""))

 

[abdulhaque]

Thanks it worked a charm. I knew the OFFSET() was to be used somehow, but haven't got to that level yet.

 

[pgc01]

You're welcome.

 

[anandkb]

Hi

"Subtotal(103,..." means it will count all cells that are not empty. Since all your cells have formulas there are no empty cells and all cells are counted.

Try:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(Table1[Flat],ROW(Table1[Flat])-MIN(ROW(Table1[Flat])),0,1,1)),--(Table1[Flat]<>""))

Not working, still counts the blank cells with formula