Summing specific values within a range based on dynamic inputs

Hello, Suppose I have a table with four columns: ID Number, Color, Country, and Price, and maybe 1000 rows. ID numbers may not be unique. I also have 3 separate cells to control parameters - one ...

Formulas & Functions

Need Help

Detlef_Lewin

Silver Contributor

Nov 08, 2017

Tom,

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.

Tom Sargeantson
Copper Contributor
Nov 10, 2017
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?
- Detlef_Lewin
  Silver Contributor
  Nov 10, 2017
  Tom,
  
  if your subscription is based on Excel 2016 then PQ is now a part of Excel. It can be found under "Get and transform".

Forum Discussion

Summing specific values within a range based on dynamic inputs