Hi all,
I have a monthly table in each worksheet and I use sumproduct for TOTAL column.
How to No duplicates in the succeeding months and to automate the master table?
Attached file
Hi all,
I have a monthly table in each worksheet and I use sumproduct for TOTAL column.
How to No duplicates in the succeeding months and to automate the master table?
Attached file
Last edited by Excel 1_2_3; 03-20-2023 at 02:25 AM.
Use
=SUMPRODUCT(SUMIF(INDIRECT("'"&$U$1:$U$2&"'!A1:A100"),$L4,INDIRECT("'"&$U$1:$U$2&"'!"&ADDRESS(1,1+COLUMNS($L4:L4),4)&":"&ADDRESS(100,1+COLUMNS($L4:L4),4))))
Change the bits in RED to suit the number of rows, but do not go mad, and make the range MASSIVE, as INDIRECT can slow your sheet down.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Pl see file for formulas .
For feb in M4
For March month change Jan:Jan to Jan:Feb in the formula.=IFERROR(INDEX($B$3:$G$14,MATCH($L4,$A$3:$A$14,0),MATCH(M$3,$B$2:$G$2,0)),0)+IFERROR(INDEX($B$17:$G$28,MATCH($L4,$A$17:$A$28,0),MATCH(M$3,$B$16:$G$16,0)),0)+SUM(Jan!M4)
Feb month table to be copied to other months.
Range size of all tables kept same.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
worksheet or Tab name : Feb
Cell M4 formula , Drag down and across
Formula:=SUM(SUMIF(INDIRECT({"Jan";"Feb"}&"!A:A"),$L4,INDIRECT({"Jan";"Feb"}&"!R1C[-11]",)))
Last edited by wk9128; 03-20-2023 at 04:45 AM.
Many Thanks Glenn Kennedy, kvsrinivasmurthy, wk9128 !!
Very much appreciated. !!
@Excel 1_2_3 You're Welcome. Glad to help . Thank You for the feedback and rep.
Power Querylet Source = Excel.Workbook(File.Contents("C:\Users\seven\Documents\Documents\10_60.xlsx"), null, true), #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7"}), #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([PRODUCT] <> "PRODUCT")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Jan"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"PRODUCT"}, {{"10", each List.Sum([10]), type number}, {"20", each List.Sum([20]), type number}, {"30", each List.Sum([30]), type number}, {"40", each List.Sum([40]), type number}, {"50", each List.Sum([50]), type number}, {"60", each List.Sum([60]), type number}}), #"Replaced Value" = Table.ReplaceValue(#"Grouped Rows",null,0,Replacer.ReplaceValue,{"10", "20", "30", "40", "50", "60"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each [10]*10+[20]*20+[30]*30+[40]*40+[50]*50+[60]*60) in #"Added Custom"
Many Thanks Czeslaw !!
Very much appreciated. !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks