sumif across multiple worksheets without referencing each one

ExcelPass

New Member
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
Ok, I'd be confident that's achievable in Power Query too - can you share some screenshots of what you need?
 

ExcelPass

New Member
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Denzo36. These screenshots show what I am looking for. Many thanks for offering help. Mike
Capture1.PNG


Capture2.PNG

Capture3.PNG

Capture4.PNG

Capture1.PNG
Capture1.PNG
Capture2.PNG
Capture3.PNG
Capture4.PNG
 

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top