Sum across sheets with condition

[dluhut]

Hello,

I'm trying to sum across several sheets with condition.

May I know how to do it?

For example, I have sheets that setup as 'Start', 'Sheet1', 'Sheet2', ... ... , 'Sheet99', 'Sheet100', 'Stop'.

Usually, a simple formula below will sum the whole sheets that's from 'Start' to 'Stop' of cell A1.

Please Login or Register  to view this content.

My question now is, if I want to sum ONLY IF a condition is met, how do I go about doing it?

I tried the formula below and it's giving me #VALUE! error

Please Login or Register  to view this content.

By the way, I'm TOTALLY AVOIDING using the Indirect function. Reason being, upper level management would like to see something, and by removing the 'Sheet x' where x is 1 to 100 outside of 'Start' or 'Stop', they're able to see the calculated values instantly. If I were to use INDIRECT function, that means that for sheets that are removed outside of the range of 'Start' to 'Stop' sheet, it'll still get's calculated

[Tony Valko]

Not possible without using the INDIRECT function.

[MrShorty]

The SUMIF() function does not support 3D references, so you need a different approach to do conditional sums.

Are you willing to use helper cells (gasp!)? I have frequently thought that the easiest way to do a conditional 3D sum is to have a conditional sum on each tab, then use a simple sum function with the 3D reference on the summary tab.

[dluhut]

Thanks for the reply.

How would the helper cells be like? Can you give a general idea?

[MrShorty]

It should look very similar to the SUMIF() function you posted (without the tab names for the 3D reference): =SUMIF($AD$17:$AD$1000,$A42,$AA$17:$AA$1000)