sumif across multiple worksheets without referencing each one

[ExcelPass]

Hello, I have in excess of 30 identically formulated worksheets that contain (amongst much other information) lists of stock, location and quantities in different columns. I want to create a single summary page that searches through all the worksheets between a Blank Start worksheet and a Blank End worksheet and sumif identical stocks across all the worksheets as well as filter where the location for that stock is. The worksheets are refreshed each week.

I had historically used sumif for this kind of thing across multiple worksheets but there are now too many creating an unwieldly sumif...+sumif..+sumif.....etc. I cam across the function =SUM(‘FirstSheet:LastSheet’!A1) which is perfect in being able to sum the same cell across a large number of worksheets but does not work for me as the stock can be multiple times anywhere in each worksheet. Ideally I am looking to find a sumif solution that works in a similar way between start and end worksheets that employs standard range settings for each worksheet on which to sum as instructed.

I would appreciate any advise there is. I know there may be better ways of creating worksheet source information to help me, but this is firmly out of my control.

thanks

Mike

 

[denzo36]

Hi, if your sheets are all identically formatted and you want to essentially combine them all, Power Query is the easiest way combine all sheets into one and then you can use a pivot table to summarise the info. Have you used power query before?

Can you share screenshots of your situation or if not possible, some fictitious data that is in the same format as your situation so we can see what you are working with?

 

[ExcelPass]

Thank you Denzo36, I haven't used Power Query but willing to learn if there isn't an excel function available. I guess the other reason why I am trying to find a native excel solution is that each worksheet sheet also has a different % factor that I want to multiply the quantity value with and I perceive that if I can find a sumif way of doing what I want then I can easily add an absolute reference cell to multiply against for each sheet.

 

[denzo36]

Ok, I'd be confident that's achievable in Power Query too - can you share some screenshots of what you need?

 

[ExcelPass]

Thanks Denzo36, I will create a scrubbed excel sheet that explains what I am trying to achieve and post. Thank you very much. Mike

 

[ExcelPass]

Hi Denzo36. These screenshots show what I am looking for. Many thanks for offering help. Mike

 

[denzo36]

Hi there - the first bit where you find the total for each month for each item is easy enough to do if you combine all sheets together with power query and build a pivot table from the result

Im not sure how the percentage column works in each sheet, could you explain?

Also can you confirm - your sheets are called stock1, stock2 etc but your summary sheet has item1, item2 etc - are stock1 and item1 the same thing?

 

[ExcelPass]

Thanks,
Stock1 is not connected in any way, they can be called anything and actually refer to different UK locations.
The % is a stocking factor. I dummed down the numbers for simplicity as the real ones are in the 100s/1000s. The way I want the summary to work for example in Item A, Month 1 (Cell D2) across all 3 Stock worksheets is...(80% x 15) + (75% x 15) + (90% x 15) = 36.75. (Note Item A appears twice in each stock worksheet. This can be random hence I used a sumif range.

Question, can the power query accomodate a large numbers of worksheets without specifying how many worksheets or each one. I quite liked the FirstSheet:LastSheet Sum for this reason, but obviously not versatile enough for this purpose.