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.Please Login or Register to view this content.
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:Please Login or Register to view this content.
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 QueryPlease Login or Register to view this content.
Many Thanks Czeslaw !!
Very much appreciated. !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks