Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ignoring Error in Subtotal (Excel 2003)

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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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,
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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