Total Sum from Multiple Sheets

[sickreto]

Hi,

I am looking for a way to find the total sum of an entered value coming from a multiple sheet only on a matched entry.

This is what I'm trying to achieve, in my attached sample wb, under the Summary sheet A2:A17 it needs to match from all other sheets (I have more sheets than the sample wb) in the same column and range.

I need to find the total sum if the customer name match the same customer name on all other sheets, but need to exclude duplicates (Customer name), zeros, empty cells and also to exclude Holiday and sick leave wording.

I have a sample answer under summary sheet in column C and D.

Any suggestions are greatly appreciated.

Thank you in advance.

[wk9128]

worksheet or Tab name : Summary

Cell C2 formula , Drag down and across

=SUM(SUMIF(INDIRECT("FName"&ROW($1:$5)&"!A:A"),$A2,INDIRECT("FName"&ROW($1:$5)&"!R1C",)))

[Czeslaw]

Power Query

let
    Source = Excel.Workbook(File.Contents("C:\Users\seven\Downloads\Sample WB.xlsx"), null, true),
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"FName1", type text}, {"Customer Name", type text}, {"Column3", type any}, {"Set 1", type any}, {"Set 2", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Customer Name] <> null and [Customer Name] <> "Customer Name" and [Customer Name] <> "HOLIDAY" and [Customer Name] <> "SICK LEAVE")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Customer Name"}, {{"Set1", each List.Sum([Set 1]), type number}, {"Set2", each List.Sum([Set 2]), type number}})
in
    #"Grouped Rows"