Sumif across several worksheets

[twaccess]

This is the beginning of a monster of a formula (to me at least) which searches 30 worksheets for the occurrence of a code before adding up the total value for each code. Is there a more elegant way of doing this that picks up all the worksheets without having to name each one within the formula?

=SUMIF('2 Pers SbS AE Screen'!C:AE,Products!A3,'2 Pers SbS AE Screen'!AE:AE)+SUMIF('3 Pers SbS AE Screen'!C:AE,Products!A3,'3 Pers SbS AE Screen'!AE:AE)+SUMIF('4 Pers SbS AE screen'!C:AE,Products!A3,'4 Pers SbS AE screen'!AE:AE)++SUMIF('4 Pers AE Screen'!C:AE,Products!A3,'4 Pers AE Screen'!AE:AE)+SUMIF('6 Person AE Screen'!C:AE,Products!A3,'6 Person AE Screen'!AE:AE)

Any help gratefully received.

T.

[ExcelFixer]

You could try naming the ranges within each sheet e.g.:
=SUMIF(RANGE2,"=a",RANGE3)
As long as you keep the names unique you will be able to sum across your sheets without issue.

Let me know if you need help with named ranges.

The Excel Fixer

[benishiryo]

hi twaccess. i supposed the item in Products!A3 can be found in just column C of the other sheet? if so, you don't have to range C:AE. you can use 2 methods. one is to do a SUMIF in each individual sheet & then summing them up in the consolidated sheet. the other is to use 3D SUMIF. you do this by typing the worksheet name say in D1:D5 of the consolidated sheet. then:
=SUMPRODUCT(SUMIF(INDIRECT("'"&D1:D2&"'!C:C"),Products!A3,INDIRECT("'"&D1:D2&"'!AE:AE")))

you can get an idea here too:
http://www.mrexcel.com/forum/excel-q...-indirect.html

[twaccess]

Sorry guys, I must be really thick because I'm just not getting it. I spent an hour last reading the above and the links. Then a few more minutes this morning. Unfortunately, I'm no further forward.. thank you anyway. I'm probably going to have to do this the long way round.

[twaccess]

Gawd... its funny how you say you can't do something... then a few moments later, it happens... you get it... I think. I made the formula work at least in the example. Thanks again.

[twaccess]

Gawd... its funny how you say you can't do something... then a few moments later, it happens... you get it... I think. I made the formula work at least in the example. Thanks again.

[twaccess]

This formula has only gone and worked across 70 worksheet pages !!

=SUMPRODUCT(SUMIF(INDIRECT("'"&Worksheets&"'!c:c"),A4,INDIRECT("'"&Worksheets&"'!b:b")))

I also learned how to create a list of Worksheet names which I then put into a range name called 'Worksheets'

I think I've found a new love... Indirect...

[ExcelFixer]

Can you mark the post as solved please? Its in the thread tools.

[twaccess]

Can you mark the post as solved please? Its in the thread tools.

Sorry about that. I was accessing via my iPhone for a few days which the tools didn't appear for some reason. All done and thanks again.

[Tony Valko]

I also learned how to create a list of Worksheet names which I then put into a range name called 'Worksheets'

Like this...

List worksheet names using a formula

[twaccess]

Like this...

List worksheet names using a formula

Thank you Tony, I lost my original copy of this post. Bookmarked now

[Tony Valko]

Good deal. Thanks for the feedback!