How to sum cell accros range of sheets

[If...Then...]

Hope someone can help.

In Excell 2003: I need to sum up values in the same cell accross a range of sheets but only if a condition (only one condition) is met.

Let me explain: i have a range of sheets that i bracketed on both ends with a blank sheet.

so the arrangement is summary sheet, blankfirst sheet, a number of other sheets, blanklast sheet. If i wanted to sum up let say cell D20, on the summary sheet i would say =sum(blankfirst:blanklast!d20) and get a sum of values in this cell accross all sheets. easy enough.

what i need to do is to say sum cell D20 but pick up values only on those sheets where value of cell A5 equals the value of cell A5 on summary sheet.

i will make sure that the values of cell A5 are identical on all sheets that have it. i can make it either numeric or text with only numbers in it. All rows/columns accross all sheets will remain in place and there will be no inserts of rows/columns in the future.

what function would allow me to do this? or is there such a function? or a combination of functions? I can not use macros as this needs to display the summary at all times.

any help would be greatly appreciated. Thank you.

[NBVC]

the easiest way would be to assign another constant cell on each sheet a formula that checks cell A5 against the Summary!A5 and return a 0 if false, and the value if true. Then you can use the same formula except referencing the new cell.

Otherwise, here is a more complex alternative solution:

http://www.xl-central.com/sum-single...le-sheets.html

[If...Then...]

NBVC thank you for your answer. unfortunately, I will be adding new groups of tabs on a regular basis and need to create a formula that does not need updating (or a table associated with it) every time there is a new addition. I could maintain it, but the user needs to have it mainenance free. they can add sets of pages by duplicating an existing set, but i don't want them altering the tables or the formulas.

I created a file that shows what i want to do, hoping that will make it clearer. this file will have a new set of SumX to LastBlankX on a regular basis and the tabs within might/might not contain the same ABCD etc tabs.

hope it makes it clearer.

[NBVC]

I've gotta take off.. but I am thinking that you will need some VBA for this... and note user will have to enable macros for VBA to work....

Perhaps a VBA expert can chime in to assist?

[If...Then...]

I am going to mark this solved. The solution offered is simple enough that users can be trained to maintain the table. Thank you very much for your help.