Sumif using indirect function

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows
I have a series of tabs, all of which are named by week end date. I also have a totals tab. What I want to do is have the totals tab autosum all of the other tabs even when new tabs are added.

All tabs are formatted the same, Is the best function to use here Sumif with indirect and if so how do i write this formula?

For the below I would like the formula to represent everything under the car type on the other tabs. Tabs are named 02.06.17, 25.05.17, 18.05.17 for example. and the Sales column is column C on all of these.



TypeSales
Mazda=formula

<tbody>
</tbody>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Best way to do this is to create a list of sheet names and give a range name (I used Tabs). Then use this formula...
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!T6:T1000"),C1,INDIRECT("'"&Tabs&"'!M6:M1000")))
 
Upvote 0
Best way to do this is to create a list of sheet names and give a range name (I used Tabs). Then use this formula...
=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!T6:T1000"),C1,INDIRECT("'"&Tabs&"'!M6:M1000")))

That is exactly what I need, thanks so much for that, Life saver!
 
Upvote 0

Forum statistics

Threads
1,215,261
Messages
6,123,942
Members
449,134
Latest member
NickWBA

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