SUMIF for all sheets between 2 specified sheets

W Joel

New Member
Joined
Feb 7, 2021
Messages
7
I am new to spreadsheets and have no training. My knowledge is very limited. Screenshots of the spreadsheet I’m creating are attached. I am attempting to total all miles and fuel by state on sheet “Quarterly IFTA Report” for all the sheets between the sheets “Start” and “Stop” Additional sheets between “Start” and “Stop” are added regularly. I have read that I can use =SUMIF. But when I do so I can only get it to work for a single sheet. Can someone please help me with the correct method and formula to accomplish this? I would greatly appreciate the assistance.
 

Attachments

  • Screenshot from 2021-02-07 10-13-50.png
    Screenshot from 2021-02-07 10-13-50.png
    67.3 KB · Views: 8
  • Screenshot from 2021-02-07 10-14-16.png
    Screenshot from 2021-02-07 10-14-16.png
    102.4 KB · Views: 8

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

W Joel

New Member
Joined
Feb 7, 2021
Messages
7
Thank you Fluff! I did see this option. As I understand it I would need to constantly update the list of sheets. Is there a way to do this without having to manually update the list of sheets? For example if I were only wanting to total a specific cell across multiple sheets (and be able to add sheets as I go without manually updating a list of named sheets) I could use =SUM between sheets and it would look like this - =SUM($Start.W16:$Stop.J16). Is there a formula which would allow me to use =SUMIF for all sheets between 2 specified sheets without manually updating the list of sheets each time a new sheet is added? Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,865
Office Version
  1. 365
Platform
  1. Windows
Unfortunately there is no way to do 3D sumifs.
One option would be to do the sumifs on each sheet & then use a 3D sum formula on the Report sheet.
 

W Joel

New Member
Joined
Feb 7, 2021
Messages
7

ADVERTISEMENT

Unfortunately there is no way to do 3D sumifs.
One option would be to do the sumifs on each sheet & then use a 3D sum formula on the Report sheet.
You just went way over my head. To be honest, I'm doing good just understanding how to =SUM across sheets. Is there an easy to understand tutorial that could help me out or a sample formula that I could edit for my spreadsheet? Thank you Fluff! I really appreciate the help :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,865
Office Version
  1. 365
Platform
  1. Windows
On each sheet between Start & Stop you could put the list of states in (for instance) N4 downwards & in O4 you could put a sumif for the miles on that sheet & in P4 out a sumif for the fuel.
Then on your report sheet you could use in C4
Excel Formula:
=SUM(Start:Stop!O4)
and fill down
 
Solution

W Joel

New Member
Joined
Feb 7, 2021
Messages
7

ADVERTISEMENT

Oh! OK..... I think I get that :) I'll work on it and let you know if I'm successful and mark the thread as solved. Thank you again Fluff!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,865
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

W Joel

New Member
Joined
Feb 7, 2021
Messages
7
I got really close but I think I missed something. I have screen shots of the 2 sheets (1000 & 1001) that should be totaling on the report sheet. I have 2 screenshots of the report sheet. I noticed it is using "O4" from the reports sheet instead of all "O4" from the sheets between "Start" and Stop" Can you see what I have done wrong?
 

Attachments

  • Screenshot from 2021-02-07 12-22-26.png
    Screenshot from 2021-02-07 12-22-26.png
    144.8 KB · Views: 0
  • Screenshot from 2021-02-07 12-22-07.png
    Screenshot from 2021-02-07 12-22-07.png
    148.2 KB · Views: 1
  • Screenshot from 2021-02-07 12-21-37.png
    Screenshot from 2021-02-07 12-21-37.png
    72.9 KB · Views: 1
  • Screenshot from 2021-02-07 12-26-43.png
    Screenshot from 2021-02-07 12-26-43.png
    93.6 KB · Views: 1

W Joel

New Member
Joined
Feb 7, 2021
Messages
7
I got it!!!!! For C4 on the report sheet =SUM($Start.O4:$Stop.O4) and then the same going down =SUM($Start.O5:$Stop.O5)
 

Attachments

  • Screenshot from 2021-02-07 12-45-56.png
    Screenshot from 2021-02-07 12-45-56.png
    73.5 KB · Views: 3

Watch MrExcel Video

Forum statistics

Threads
1,130,149
Messages
5,640,400
Members
417,140
Latest member
whiteprose

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