Is there a way in Excel to get a sum of the max values in multiple tables summing only the values with a certain identifier?

[Red97]

Hey, sorry if this is convoluted.

I have multiple tables in a worksheet which has income targets for each month of the year. Each table relates to a different branch of the company and each row has a specific income stream (for example a twitch streamer's rows might have subscribers, donations, sponsors etc.). Every table has the same income streams.

I need to get the sum of the max value of each income stream so that the end result gives me the total income target I'm expecting from the sum of all branches' "subscribers" stream, for example. Since the data will be used for monthly reporting I shouldn't need to use the date to differentiate by month, the max value of the row should always be the current month.

Another caveat is that some tables are from different parent companies. These sums would need to be separated. In the example given, imagine twitch and youtube both fall under the parent "Streaming", while other tables might fall under "PC repairs", and I'd need a sum of the max values for each separately, so the "Donations" total for the PC repairs should not be included in the "Donations" total for Streaming.

This would be relatively easy to do manually except that there is a large amount of tables and it would be very time consuming to do, especially every month of the year.

Is there any way to do this in excel? I've added an example picture to show the layout of the dataset, the vals at the bottom are the intended output.

Thank you, please feel free to let me know if I need to clarify or change anything

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [Column1] <> "TOTAL TARGET"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Column1] <> null),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Column1"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Income Stream"}, "Attribute", "Value"),
    #"Filtered Rows2" = Table.SelectRows(#"Unpivoted Other Columns", each [Attribute] = "23-Aug"),
    #"Grouped Rows" = Table.Group(#"Filtered Rows2", {"Income Stream"}, {{"Totals", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

 

[alansidman]

A shorter more efficient Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Income Stream", "23-Aug"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Income Stream] <> null),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Income Stream"}, {{"Totals", each List.Sum([#"23-Aug"]), type nullable number}})
in
    #"Grouped Rows"

 

[Red97]

A shorter more efficient Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Income Stream", "23-Aug"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Income Stream] <> null),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Income Stream"}, {{"Totals", each List.Sum([#"23-Aug"]), type nullable number}})
in
    #"Grouped Rows"

Thank you for this. Would there be a way to make the source work with multiple tables? They're all currently separated. I'm sure I can probably do something like creating one big table if not so should be good either way, thank you

 

[alansidman]

Show an example of multiple tables. There is probably a way, but without seeing a layout of sample data it is difficult to provide a solution. Appending the tables to each other may simplify the solution.

 

[Red97]

Show an example of multiple tables. There is probably a way, but without seeing a layout of sample data it is difficult to provide a solution. Appending the tables to each other may simplify the solution.

Sorry again for the late reply, I've been on holiday for the past week. I've had another look and the output data I'd been sent wasn't in tables at all, I've just manually set up a table containing all of the "tables" I have, and added an extra column to determine which umbrella they're under, as well as altered the query to account for this.

Thanks so much for your replies and help, having never used power query I was extremely confused on where to start but the above code works perfectly for my needs.

I'll resolve the thread now, apologies for not doing so sooner.

 

[alansidman]

thanks for the feedback.