Subtotals

[Douglas]

Somebody posted a really nice formula recently =SUMPRODUCT
(--(TRIM(E10:J10)<>"")) that will count the cells that
have a character in the cell but will not count if the
cell has a only spacebar keystokes in it. This is much
better than CountA() because CountA() would also count the
erroneous spacebar cells.

Question: How can you use this formula down a column like
a Subtotal() function so if you have a filter set it will
only look at the items in the filter.

Thank you for your help.

Douglas

 

[Peo Sjoblom]

One way

=SUMPRODUCT((TRIM($B$2:$B$20)<>"")*(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$20)-M
IN(ROW($B$2:$B$20)),,))))

 

[Douglas]

All I can say is WOW. Thanks.

-----Original Message-----
One way

=SUMPRODUCT((TRIM($B$2:$B$20)<>"")*(SUBTOTAL(3,OFFSET ($B$2,ROW($B$2:$B$20)-M
IN(ROW($B$2:$B$20)),,))))

--

Regards,

Peo Sjoblom





.