Forum Discussion
Solved
sum index match from different tabs into a consolidated tab
Hi everyone,
I have similar tables in 4 different tabs. Each referring to a business line. The table is composed of various rows with a client number (e.g. Customer 1, Customer 2 and so forth) and of columns for each financial year (e.g FY21, FY22 and so forth). Many of the customers appear in all 4 tables or at least in 2 of them. I want to have a 5th tab where I can consolidate each customers’ sum in each table. So for example if Customer 11 is showing a value for FY21 in every table, I want to consolidate the sum of those values in the Consol Tab, and want to do it for every FY column. I tried wiriting index/match formulas for each tab into the Consol Tab and then adding them but all i get is “N/A”.
Can somebody please help! Thank you very much
I would always suggest having a single consolidated database as the first step for something like this. So that's what I've created. (It is always easier for both you, the user, and Excel to work from a consolidated database. It is easier--simpler formulas--to parse data into those separate categories, for example, via formula or Pivot Table, rather than separating them at the start (as we would have done back in the pencil and paper days, on ledger sheets) .
From that consolidated database it's very easy to take one of two different approaches to summarizing the data.
- Perhaps the easiest is to use the Pivot Table, which will allow you to be selective, if desired, by product category (see the drop down selection at the top of the Pivot Table tab).
- The other is a formula that SUMs FILTERed results for each corporation for each column. You'll see that formula in your original "Top Customer -- Console" tab
I don't pretend that this is the complete answer -- the results aren't sorted based on highest quantities -- but it's a start and gives you an introduction to two quite different ways to approach the situation.
This solution stacks the matrices and rolls them up with REDUCE. This formula gets a bit cleaner with the forthcoming GROUPBY and the ability to use eta lambdas.
Named items were defined, too.
=LET( stack, VSTACK(Prevention, EAP, Recovery, Digital), Corp, TAKE(stack, , 1), FY, DROP(stack, , 1), RollUp, LAMBDA(acc, v, LET( record, FILTER(FY, Corp = v, 0), total, BYCOL(record, LAMBDA(col, SUM(col))), VSTACK(acc, total) ) ), DROP(REDUCE("", AllCorps, RollUp), 1) )It would be a LOT easier to help you if, in addition to your verbal description of what you have and what you want, you could actually post a copy of the actual workbook. Recognizing that the actual workbook might well include confidential information--real names of real customers for example--if you could instead post a copy of a facsimile of the actual, in which you've replaced real names with fictitious ones.
Post it on OneDrive or GoogleDrive with a link here that grants access.
I would always suggest having a single consolidated database as the first step for something like this. So that's what I've created. (It is always easier for both you, the user, and Excel to work from a consolidated database. It is easier--simpler formulas--to parse data into those separate categories, for example, via formula or Pivot Table, rather than separating them at the start (as we would have done back in the pencil and paper days, on ledger sheets) .
From that consolidated database it's very easy to take one of two different approaches to summarizing the data.
- Perhaps the easiest is to use the Pivot Table, which will allow you to be selective, if desired, by product category (see the drop down selection at the top of the Pivot Table tab).
- The other is a formula that SUMs FILTERed results for each corporation for each column. You'll see that formula in your original "Top Customer -- Console" tab
I don't pretend that this is the complete answer -- the results aren't sorted based on highest quantities -- but it's a start and gives you an introduction to two quite different ways to approach the situation.
- Cool, I will do that now.
