SUM through various sheets!

[Deepak Sharma]

Hello everyone,

I need to sum all the products from different sheets. There are around 500 products. Although I have added them through Sum & Sumproduct functions but that is a very lengthy & time-consuming formula. I have applied it in Column D of Order Tab.

Is there any short formula to be used to sum in one go? Please apply it in column E, if it's possible! Thanks !!!

Please see the attached sheet !!

Regards,
Deepak

 

[bosco_yip]

E2, copied down :

=SUMPRODUCT(('OR013925'!$D$2:$D$85='Order Tab'!C2)*'OR013925'!$H$2:$H$85+('OR014014'!$D$2:$D$85='Order Tab'!C2)*'OR014014'!$H$2:$H$85+('OR014104'!$D$2:$D$85='Order Tab'!C2)*'OR014104'!$H$2:$H$85+('OR014102'!$D$2:$D$85='Order Tab'!C2)*'OR014102'!$H$2:$H$85)

Regards
Bosco

 

[Ramesh]

You can take the help of 3D reference formula feature of Microsoft Excel..

Please find the attached worksheets where I have written the formula..

The steps followed by me are:

1) First create a named range, which contains a list of worksheet names that needs to be included in the formula
2) Using this named range use SUMIF & SUMPRODUCT functions to get the required output..

Note: This is not an Array formula i.e. you need not press Ctrl+Shift+Enter after typing in the formula..

Hope this simplifies your formula...

 

[Deepak Sharma]

Thank you so much, Bosco & Ramesh.

Yes, Ramesh, this is what I was looking for...Awesome formula! Thanks for your help, It will reduce a lot of time consumption!

Regards,
Deepak Sharma

 

[Ramesh]

Hello Deepak.. glad that I could help..

just wanted to let you know that ..if you would like to add / delete any worksheets.. all that you have to do is, modify the named range where the worksheet names are specified..that should automatically include any new/ deleted worksheets in your formula..as long the the column references remains unchanged this 3D reference formula would produce desired results..

 

[Deepak Sharma]

Yes Thanks, Ramesh !! It's very nice to learn all this!