Nov 09 2019 11:41 AM
Nov 10 2019 12:04 PM
@Sagar008 I’m sure there are people here who would be happy to help. But you need to give a much more complete description of what you’re trying to do.
if you have the workbook in question and can upload it as well, that would also be helpful.
Nov 13 2019 08:53 AM
Hi@mathetes .....In the 3 Workbooks attached,
1. Summary WB
2. Month WB
3. Customer WB
CASE 1
Consider I have a Summary sheet In which the Customer wise sales summary has to be summarised from Month Workbook for month of October for Year 2019. Consider in Month workbook, I have the Keywords i.e Month name and year in cells B1 and B2 which always remain in the same position. The data table columns also always remain in the same sheet columns. I wish to know, how with a Single Sumproduct and reference function, I can summarize the data from different Month sheets onto the summary sheet with the month value to be considered for Month name and year in the summary sheet. Also consider that as for each month new Month sheet in the workbook is added, how can 1 Sumproduct and reference function work for new sheets added in the workbook.
CASE 2
For the Same summary sheet, I wish to Summarize the data from Customer Workbook for month of Oct 2019, considering here the Key is customer name present in column B1 of every sheet of Customer Workbook. Also Consider, for each new customer added, a new Customer sheet is added.
Regards
Sagar
Nov 13 2019 10:53 AM
@Sagar008 I used the function DSUM instead of SUMPRODUCT. I have personally never used SUMPRODUCT, which doesn't mean at all that it would be inappropriate. It may well be that there are others here at this site who would recommend and give examples of how SUMPRODUCT would serve you better, and I would welcome their input.
I find DSUM to work well for the kind of data extraction you're trying to do. In the attached, I've done two things that I want to draw your attention to.
Here, from Excel Help, is the "generic" way to understand DSUM: DSUM(database, field, criteria)
So breaking down the formula I use, you can see that
database is 'Customer Workbook(Example).xlsx'!CustDB
field is "Sales" -- it's important to recognize that the field name always belongs in quotation marks
and criteria is G4:J5
The criteria range is where you specify the customer, the month and year, and product...DSUM then gives you the total of sales from the database for all rows that meet the criteria. You can change any one of those to see how it affects the number reported.
I stopped at this point because I realize I'd be changing the way you look at your data, and that may not be something you want to do. If you are willing to turn your Customer Workbook into a single sheet, combining all customer data into a single database, I think it would benefit you. Similarly the Month Workbook.... I mainly wanted to suggest re-thinking how you do this.
And I'd be perfectly happy if somebody else has a way to use SUMPRODUCT with your sheets as you have them. Your choice.
Nov 13 2019 11:02 AM
Nov 13 2019 11:06 AM
Nov 13 2019 12:03 PM
@Sagar008I'm truly sorry to hear that. I'll have to defer then to somebody else who has more experience with the kind of process you describe.
But if I were in your situation, I'd be at least looking to see how feasible it would be to change the way those daily inputs are collected. Are all of them being sent by people in the same company? If so, isn't there some way you could all be sharing and maintaining the same worksheet, simply adding rows to the bottom of a single database? Logistically, having to collect from (how many) multiple sources and then extract the summary data...... Not easy! I'm sorry I can't help further.
Nov 13 2019 12:14 PM - edited Nov 13 2019 12:14 PM
SolutionI think where you're going with this is a 3D reference which is possible in Excel but limited to a set list of functions.
SUMPRODUCT is not among these functions. In you're setup you'll be creating external links as long as the data is not consolidated. Have you considered rolling the data up through queries then pulling the summary from there?
Apr 22 2020 05:40 AM
Hi @Patrick2788
From here I got onto the journey of Power query and Data modelling.
Thanks a ton for understanding.
Kudos
Nov 13 2019 12:14 PM - edited Nov 13 2019 12:14 PM
SolutionI think where you're going with this is a 3D reference which is possible in Excel but limited to a set list of functions.
SUMPRODUCT is not among these functions. In you're setup you'll be creating external links as long as the data is not consolidated. Have you considered rolling the data up through queries then pulling the summary from there?