Ignoring Error in Subtotal (Excel 2003)

Topic

New Reply

Hi

I use an array formula like this:{=SUM(IF(ISNUMBER(D2:D8),D2:D8))}
which will excludes errors.
How can I use this to apply in a subtotal function?

TIA

Reply | Quote

Replies

No, if you need that, you’d be better off using SUM and IF.

Reply | Quote

Hi Hans

This will do the trick but what how does this formula exactly means

=SUM(IF(ISNUMBER($B$2:$B$5),$B$2:$B$5)*(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$5)-MIN(ROW($B$2:$B$5)),,))))

TIA

Reply | Quote

This bit:
OFFSET($B$2,ROW($B$2:$B$5)-MIN(ROW($B$2:$B$5)),,)

returns an array of the individual cells in B2:B5
The Subtotal(3,..) bit is the equivalent of COUNTA and returns an array of 1s or 0s depending on whether the cell is visible or not due to the filter.
This array of 1s and 0s is then multiplied by the array returned by:
IF(ISNUMBER($B$2:$B$5),$B$2:$B$5)

so you only get the values that are visible and are numbers.
Does that make sense?

Reply | Quote

Hi Rory

Thank for the explaination. This is a little above me now, I will explore these intermediate formulas individually.

cheers,

Reply | Quote