Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Summing Filtered Items (2003)

    OK, I have three columns of data (B-D, with each column containing both positive and negative numbers-no text), and I have autofilter on, filtering the columns for certain criteria that I have set out in column A. What I would like to do is sum all of the positive numbers in a filtered column, and all of the negative numbers in a filtered column, separately. That is, once I have autofilter on, I would like to sum, in a cell below the data in, say, column C, all of the positive values in the "filtered" column C, and in another cell, all of the negative values in the "filtered" column C. Any thoughts?.

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

    Re: Summing Filtered Items (2003)

    If you want to add items in a filtered column, you can use the SUBTOTAL function. But that won't let you specify an extra condition, and I don't see a way to do that without VBA.

    Instead of using AutoFilter, I'd set up criteria ranges and use the DBSUM function - look it up in the Excel help.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summing Filtered Items (2003)

    Hans,
    Thanks for your reply. Unfortunately, when I did a search in Excel Help, it gave me zero results for DBSUM. Any thoughts?
    Thanks again.

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

    Re: Summing Filtered Items (2003)

    I have no idea why you can't find DBSUM, but I found a formula to do what you want. It's based on the one found in Excel: Conditional add/count on filtered data.

    For example, to sum positive filtered items in C2:C17, you can use this array formula (confirm with Ctrl+Shift+Enter):

    =SUM(IF(SUBTOTAL(3,OFFSET($C$2:$C$17,ROW($C$2:$C$1 7)-MIN(ROW($C$2:$C$17)),,1)),(($C$2:$C$17>0)*($C$2:$C $17))))

    To sum negative items, change >0 to <0.

    See attached sample workbook.
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summing Filtered Items (2003)

    Thanks, Hans. As usual, works like a charm!

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Summing Filtered Items (2003)

    I dunno if I'm being daft, but I too cannot locate the DBSUM function. Excel does not list it in the Paste function dialog, I have the Analysis Toolpak active, yet it still eludes me. No reference in help either?

    I do find results in the Google search, but where does one get a reference to it in Excel?
    Regards,
    Rudi

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

    Re: Summing Filtered Items (2003)

    Oh darn - I shouldn't have relied on my memory. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    The English language version of the function is DSUM, not DBSUM.

    I apologize for the confusion!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summing Filtered Items (2003)

    Hi Hans,
    One further question: Using this formula, or one similar, how would I count the number of visible, positive (non-zero) figures in an autofiltered range, as well as the visible, non-zero negative figures?
    Thanks again.

  9. #9
    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: Summing Filtered Items (2003)

    You could use:
    <code>=SUMPRODUCT((SUBTOTAL(3,OFFSET($C$2:$C$17,RO W($C$2:$C$17)-MIN(ROW($C$2:$C$17)),,1)))*($C$2:$C$17>0))</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    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: Summing Filtered Items (2003)

    PS and again, just change the <code>>0</code> to <code><0</code> for negative numbers.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Summing Filtered Items (2003)

    In the sample workbook, you can use the array formula

    =SUM(IF(SUBTOTAL(3,OFFSET($C$2:$C$17,ROW($C$2:$C$1 7)-MIN(ROW($C$2:$C$17)),,1)),($C$2:$C$17>0)*1))

    to count positive entries. Confirm with Ctrl+Shift+Enter. Similar for negative entries.

    See attachment.
    Attached Files Attached Files

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summing Filtered Items (2003)

    Thanks again, Hans. Works great!

Posting Permissions

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