Summing specific values within a range based on dynamic inputs

Question

Hello,&nbsp;Suppose I have a table with four columns: ID Number, Color, Country, and Price, and maybe 1000 rows. ID numbers may not be unique.&nbsp;I also have 3 separate cells to control parameters - one for color, one for country, and one that contains a number - lets say&nbsp;that number is 10 for this example.&nbsp;Its easy for me to sum the price column based on rows that match the country and color parameters using SUMIFS However, what I want to do is sum only the rows out of the subset of rows that match color &amp; country that have the 10&nbsp;highest ID numbers. Since ID numbers may not be unique, if there are 10 rows that match color &amp; country and have the same ID number and that ID number is the highest in the range of matches, I want to sum the prices for those 10 rows.&nbsp;Again, the number 10 may change and is defined by the value that happens to be in that parameter cell.&nbsp;To say it another way, I want to return a subset of a table based on 2 qualitative conditions, and then sum a column in that subset, only counting the rows that have the X highest values in another column.&nbsp;Any suggestions as to how to do this?&nbsp;Thanks!

detlef_lewin · Answer

Tom,&nbsp;I would suggest a Power Query solution instead of formulas.let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Color", type text}, {"Country", type text}, {"Price", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Color] = "black") and ([Country] = "UK")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"ID", Order.Descending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",3),
    #"Calculated Sum" = List.Sum(#"Kept First Rows"[Price]),
    #"Converted to Table" = #table(1, {{#"Calculated Sum"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Sum of Top3 ID numbers"}})
in
    #"Renamed Columns"The criteria are hard coded. But I'm sure the PQ-pros will tell you how to change the code to use dynamic criteria.&nbsp;

tom sargeantson · Answer

Thank you! I don't think my version of excel (365 home) has power query functionality built in. Is trying to do this with formulas impossible in your view?&nbsp;

detlef_lewin · Answer

Tom,&nbsp;if your subscription is based on Excel 2016 then PQ is now a part of Excel. It can be found under "Get and transform".&nbsp;

Forum Discussion

Summing specific values within a range based on dynamic inputs

3 Replies