Sumproduct from different Sheets on multiple workbooks based on a keyword

[Sagar008]

Sumproduct from different Sheets on multiple workbooks based on a keyword present in a particular cell location across the sheets

[#2]

@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. 

[#3]

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

[#4]

@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.

1. I turned the multiple sheets in Customer WB into a single database, and given it the name CustDB (using Insert...Name) which is what I'd recommend you do too. In working with a paper record, it might make sense to keep customer records on separate sheets but in Excel's case it makes more sense usually to create a single database of comparable data records. In this case, differentiating by a single new column, CustID.
2. I then created a single demonstration of how DSUM would get the data you want for any given customer, for any given product, for any given month. The DSUM formula is in Cell D5 of the Summary Workbook. It reads as follows:
   =DSUM('Customer Workbook(Example).xlsx'!CustDB,"Sales",G4:J5)

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.

[#5]

Hi.....I do not wish to combine the different Sheets into single sheet as the workbooks are received via mail on daily basis. I just wish to replace the old WB with new WB and want the formula to do the rest work.

[#6]

Also using the keyword from the Customer or month sheet from particular cell is very important. I.e excel reads the data from only that sheet on which the keyword (month and year) on particular cell location is present else doesn't sum data from those sheets.

[#7]

@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.

[#8 · Accepted solution]

@Sagar008

I think where you're going with this is a 3D reference which is possible in Excel but limited to a set list of functions.

https://support.office.com/en-us/article/Create-a-3-D-reference-to-the-same-cell-range-on-multiple-w...

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?

[#9]

Hi @Patrick2788 

From here I got onto the journey of Power query and Data modelling.

Thanks a ton for understanding.

Kudos