# Subtotal with function 103 to ignore blanks

#### abdulhaque

##### Board Regular
Hi all,

I have the formula (Table1[Flat] is Column D)
Code:
``=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
Code:
``=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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### pgc01

##### MrExcel MVP
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

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

You're welcome.

#### anandkb

##### New Member
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

Replies
3
Views
208
Replies
2
Views
311
Replies
9
Views
139
Replies
1
Views
232
Replies
1
Views
156

1,191,187
Messages
5,985,194
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back