SUMIF for all sheets between 2 specified sheets

[W Joel]

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.

 

[Fluff]

Hi & welcome to MrExcel.
You cannot directly use sumifs over multiple sheets, but there are workrounds.
Have a look here Excel formula: 3D SUMIF for multiple worksheets | Exceljet

 

[W Joel]

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]

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]

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]

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

=SUM(Start:Stop!O4)

and fill down

 

[W Joel]

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]

You're welcome & thanks for the feedback.

 

[W Joel]

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?

 

[W Joel]

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)