Adding multiple cells from multiple sheets with sumif function

Hi All

I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:

A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one (hopefully) succinct formula. What I use so far is:

='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82)

which is fine for totalling up any disk space changes within the first project, but not the other 9. I've attempted modifying my code like thus:

='P1:P10'!C83+SUMIF('P1:P10'!E82,"=2009 - Q1",'P1:P10'!D82)

But this doesn't work (the cell ranges are identical in each worksheet by the way).

The only way i've thought around this was to individually add each worksheets totals together, but this seems a very inefficient way of doing this.

Any suggestions as to a technique / command I could use would be greatly appreciated.

p.s. I use excel 2003 if this makes any difference.

This:

SUM('P1:P10'!C83)

would work, however, this:

SUMIF('P1:P10'!E82,"=2009 - Q1",'P1:P10'!D82)

will not... you will need to use something a little more complex I'm afraid...

if we assume that you store P1, P2 to P10 (sheet names) in say a range Z1:Z10

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z10&"'!E82"),"2009 - Q1",INDIRECT("'"&Z1:Z10&"'!D82")))

Thanks DonkeyOte - i'll give that a try (looks like i'll need to do a bit of research on INDIRECT and SUMPRODUCT functions). Just out of interest, why would you reference the cells like that rather than just popping them in directly as I did?

Cheers,

Originally Posted by mrgooding

Just out of interest, why would you reference the cells like that rather than just popping them in directly as I did?

Sorry, not sure I follow ...

Unfortunately you need to use fairly elaborate formulae to do conditional referencing across sheets... there is the morefunc.xll which has the THREED function which makes this type of thing a little easier (and arguably more intuitive!) but obviously it's 3rd party and if you distribute your file it may be tricky to implement... the solution provided in the prior post is to my knowledge the best approach available to you using native formulae...

And just to clarify what the formula is doing...

It's running 10 SUMIF's -- each iteration (1 to 10 - determined by Z1:Z10) uses the appropriate sheet name to generate the appropriate SUMIF ranges... the SUMPRODUCT is then summing the results of those SUMIF iterations.

Not the most technical of explanations but hopefully put in such a way as that it makes sense ... a bit ... :-)