# SUMIF for all sheets between 2 specified sheets

#### W Joel

##### New Member
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
67.3 KB · Views: 8
• 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
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
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

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

#### W Joel

##### New Member

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
You're welcome & thanks for the feedback.

#### W Joel

##### New Member
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
144.8 KB · Views: 0
• Screenshot from 2021-02-07 12-22-07.png
148.2 KB · Views: 1
• Screenshot from 2021-02-07 12-21-37.png
72.9 KB · Views: 1
• Screenshot from 2021-02-07 12-26-43.png
93.6 KB · Views: 1

#### W Joel

##### New Member
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
73.5 KB · Views: 3

Replies
3
Views
51
Replies
4
Views
286
Replies
1
Views
173
Replies
2
Views
112
Replies
3
Views
116

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.

### Which adblocker are you using?

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

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