+ Reply to Thread
Results 1 to 10 of 10

Calculate the sum of a dynamic filtered list in each column

  1. #1
    Registered User
    Join Date
    03-17-2020
    Location
    Texas
    MS-Off Ver
    365
    Posts
    5

    Calculate the sum of a dynamic filtered list in each column

    I would like to utilize the dynamic array functionality and create a formula that calculates the sum based on two criteria.

    The formula that I have in N3 = SUM(FILTER(FILTER(H3#,H2#=N2),(G3#=M3))). Is there a way to make this spill down and to the right?

    I have Office 365, but do not have the LAMBDA function.

    test_sheet.PNG

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Calculate the sum of a dynamic filtered list in each column

    Welcome to the forum.

    LAMBDA is available on the insider programme - join up and you'll get it now.

    Is this just an exercise in using the new functions, or would an older approach do?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    03-17-2020
    Location
    Texas
    MS-Off Ver
    365
    Posts
    5

    Re: Calculate the sum of a dynamic filtered list in each column

    I am on a corporate machine and I believe they have blocked access to the insider program and this will be a shared report amongst other users that also will not have the LAMBDA function.

    I am trying to create a dynamic report, the number of rows and columns could vary greatly. So I am trying to avoid dragging the formula.

    If there is an older approach that accomplishes this then yes that works for me!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Calculate the sum of a dynamic filtered list in each column

    You can't spill this, but paste it into the top left cell:

    =SUMPRODUCT(($G$3#=$M3)*($H$2#=N$2),$H$3#)

    Then select that cell - CTRL+C - select the entire range - right-click - paste special - formulae.

    Or just drag it. Sorry!

  5. #5
    Registered User
    Join Date
    03-17-2020
    Location
    Texas
    MS-Off Ver
    365
    Posts
    5

    Re: Calculate the sum of a dynamic filtered list in each column

    If I alter the formula in N3 to be =FILTER(FILTER(H3#,H2#=N2#),(G3#=M3)) it spills to the four columns. Instead of getting the data for each column, I would like for it to give me the sum of each column.

    revised_formula.PNG

    Are you saying that this is not possible?

    Thanks!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Calculate the sum of a dynamic filtered list in each column

    Thatís not the formula I gave you, though, is it?

  7. #7
    Registered User
    Join Date
    03-17-2020
    Location
    Texas
    MS-Off Ver
    365
    Posts
    5

    Re: Calculate the sum of a dynamic filtered list in each column

    Quote Originally Posted by AliGW View Post
    Thatís not the formula I gave you, though, is it?
    The original post asked if there is a way to avoid dragging the formula.

    In your post, you said your formula has to be dragged or copied.

    I have provided some more detail to show what I am hoping to accomplish.

    Are you thinking that it could be modified to accomplish what I want?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Calculate the sum of a dynamic filtered list in each column

    No. I asked if an older approach would do and you said that it would.

    I donít know enough yet about the new dynamic formulae to say that itís not possible, but I have not been able to do it.

    I have given a solution that requires copying the old way. Thatís the best I have - sorry

  9. #9
    Registered User
    Join Date
    03-17-2020
    Location
    Texas
    MS-Off Ver
    365
    Posts
    5

    Re: Calculate the sum of a dynamic filtered list in each column

    Thanks for the try!

  10. #10
    Registered User
    Join Date
    02-23-2021
    Location
    US
    MS-Off Ver
    365
    Posts
    3

    Re: Calculate the sum of a dynamic filtered list in each column

    So some compromise will be needed if you want a spilled array for your sums; we can unpivot your Dataset and then use SUMIFS on the unpivoted data.
    Screenshot 2021-04-30 091455.png
    In your example spreadsheet you create spilled formulas that replicate the layout of your dataset; try replacing that with this formula in H2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then you can just SUMIFs on the spilled unpivoted data in N3:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. Ranking within a filtered list, in different column.
    By hmwestenhaver in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-02-2020, 02:51 AM
  2. Replies: 6
    Last Post: 08-08-2019, 02:41 PM
  3. [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
  4. Calculate the arithmetic mean of filtered list of values
    By jackehendrix in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-13-2012, 01:42 PM
  5. Dynamic validation with list that have been auto filtered
    By Perception in forum Excel General
    Replies: 0
    Last Post: 02-08-2012, 04:05 PM
  6. Replies: 7
    Last Post: 01-06-2011, 02:57 PM
  7. Active / Dynamic Filtered List (changes w/ change in cell value)
    By trippknightly@hotmail.com in forum Excel General
    Replies: 3
    Last Post: 03-31-2005, 12:06 PM

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