+ Reply to Thread
Results 1 to 10 of 10

Using =SUMIFS on a filtered list

  1. #1
    Registered User
    Join Date
    10-05-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 MS and mac
    Posts
    26

    Using =SUMIFS on a filtered list

    Hi everyone,

    I want to be able to use a SUMIFS formula on a filtered list. So summing based on multiple criteria but only on the visible cells. I have seen a number of solutions for this which seem to use a combination of SUMPRODUCT, SUBTOTAL and OFFSET but I am struggling to understand the syntax and why these work. Was hoping someone could help me with this.

    For example one solution I have seen uses this formula:

    =SUMPRODUCT(SUBTOTAL(9,OFFSET(E$10:E$33,ROW(E$10:E$33)-ROW(E$10),0,1,1)),--($D$10:$D$33=$D5))

    I thought SUMPRODUCT used two or more same sized ranges. How does SUBTOTAL generate a range? And what does the OFFSET formula do in this formula? Also how does the last part of the SUMPRODUCT formula work - is this like an IF statement and what does the "--" do - I've never seen that in a formula before?

    Sorry this is a bit of an odd question but if I can understand the syntax then I can hopefully adapt for my situation.

    Thanks in advance.
    Last edited by Ricky Wilko; 12-19-2018 at 12:39 AM.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Using =SUMIFS on a filtered list

    For better understanding (How the syntax works)
    Stay in the Cells in which in you have above formula >
    Click on formulas from Menu Bar >
    Click on Evaluate Formula > (a window will popup)
    you will find formula just below Evaluation: here you will find underline in some part of the formula >
    Click on Evaluation >
    now will see how the formula is working (calculation part)
    Keep click on evaluation till you get final result.

    Hope you understand better the formula by doing above (by evaluating the formula)
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    10-05-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 MS and mac
    Posts
    26

    Re: Using =SUMIFS on a filtered list

    Thanks Samba, I'll give that a go.

    I guess my real question (in a roundabout way) was is it possible to use a SUMIFS formula on a filtered list - ie so it only sums the visible records. I have seen solutions with SUMIF but I need to be able to do it using multiple criteria.

    Thanks

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Using =SUMIFS on a filtered list

    I think it is not possible, I think it is possible with the combination of sum,subtotal,offset only

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Using =SUMIFS on a filtered list

    As far as I know, the only way to do SUMIFS (2 or more criteria) on a filtered list is to use SUMPRODUCT-SUBTOTAL-OFFSET

    For example:

    =SUMPRODUCT(($A$2:$A$16=F3)*($B$2:$B$16=G3)+0,SUBTOTAL(109,OFFSET($D$2:$D$16,ROW($D$2:$D$16)-MIN(ROW($D$2:$D$16)),0,1,1)))

    Red: SUMPRODUCT with a single criterion, the +0 needed to coerce TRUE-FALSE to 1,0
    Green a second criterion for SP. The +0 isn't needed, but does no harm and reminds you of the need for it in a single criterion query.
    Blue generates a list of numbers for the OFFSET, running from 0-15
    Evaluate formula doesn't work well on the next bit. You see a series of ~VALUE errors, which resolve as all of the values in column D. However, if any of the values in column D are filtered out, SUBTOTAL(109 returns zero instead of the value.

    So, the formula looks at the entire range, all the time, but replaces the calues in column D that are hidden using the filter by zero - so they do not contribute to the total.

    see the attached file.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Registered User
    Join Date
    10-05-2011
    Location
    Sydney, Australia
    MS-Off Ver
    Office 365 MS and mac
    Posts
    26

    Re: Using =SUMIFS on a filtered list

    Thanks Glenn, I really appreciate you taking the time to put this together and explain it. I'm not sure I fully understand how the syntax works (bit beyond my pay grade) but I will play around with it and see if I can get it to work for my particular circumstance.

    Thanks again and have a good Christmas.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Using =SUMIFS on a filtered list

    Do use the evalyate formula thing. It is a bit flawed, but ... it does help you understand Excel's logic. If you hit a snag, shout.

    But. For now. You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Using =SUMIFS on a filtered list

    Hi.

    I'm late to the party. If I understand correctly the filtering takes care of the SUMIFS part.

    This works at my end ... piggy backing on Glenn's upload ... a simple
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    does what I understand you to want. Apply filters as desired.

    I thought SUMPRODUCT used two or more same sized ranges. How does SUBTOTAL generate a range? And what does the OFFSET formula do in this formula? Also how does the last part of the SUMPRODUCT formula work - is this like an IF statement and what does the "--" do
    SUBTOTAL doesn't generate a range. It generates an array in this case (revealed by Evaluate formula). The same size rule still applies.

    OFFSET can "sub-divide" a range into smaller sub ranges. SUBTOTAL accepts this since ranges are the only thing SUBTOTAL will process.
    Dave

  9. #9
    Registered User
    Join Date
    11-19-2019
    Location
    Gdansk
    MS-Off Ver
    Office 365 ProPlus
    Posts
    1

    Re: Using =SUMIFS on a filtered list

    Hi, just wanted to thank you for the conditional sum of the visible range, that was the thing I was looking for for more than hour - the combination of Sumproduct, Subtotal and Offset works perfectly.

    BR
    Piotr

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Using =SUMIFS on a filtered list

    @ rozzpl

    Welcome to the forum.
    Glad we could help. Thanks for first researching us for this. It validates the "good thread titles" rule. You get 'repped'.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count Values in Filtered List / Return Final Row in a Filtered List
    By HarryGreenwood in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-22-2018, 06:52 AM
  2. Sumifs only on filtered results
    By annaisakiwi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2018, 10:19 PM
  3. [SOLVED] Sumifs based on filtered data
    By amartin575 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2018, 08:04 PM
  4. Subtotal Sumifs + filtered fields.
    By cf123 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-28-2014, 09:23 PM
  5. [SOLVED] Excel VBA - Issue in Naming Filtered Range on a Filtered List.
    By Vinod Krishna.C in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-06-2014, 01:17 PM
  6. [SOLVED] Using sumifs for a filtered range
    By liranbo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-01-2012, 12:09 PM
  7. sumifs and filtered data
    By finance14 in forum Excel General
    Replies: 3
    Last Post: 12-22-2011, 11:36 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1