# Thread: Ignoring Error in Subtotal (Excel 2003)

1. ## Ignoring Error in Subtotal (Excel 2003)

Hi

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

TIA

2. ## Re: Ignoring Error in Subtotal (Excel 2003)

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

3. ## Re: Ignoring Error in Subtotal (Excel 2003)

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

4. ## Re: Ignoring Error in Subtotal (Excel 2003)

This bit:
<code>OFFSET(\$B\$2,ROW(\$B\$2:\$B\$5)-MIN(ROW(\$B\$2:\$B\$5)),,)</code>

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:
<code>IF(ISNUMBER(\$B\$2:\$B\$5),\$B\$2:\$B\$5)</code>

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

5. ## Re: Ignoring Error in Subtotal (Excel 2003)

Hi Rory

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

cheers,

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•