sum numbers over several sheets with varing starting / ending point

Hi there (first post, please be lenient),

In every sheet (Sheet1, Sheet2, Sheet3) I have one value in cell B2. I add these B2's like this:

=SUM(Sheet1:Sheet3!B2)

Now, the names of the Sheets can vary and sometimes I only want to sum from Sheet1 to Sheet2.

Therefore I write in cell A1 the name of the first sheet where I want to start summing and in A2 the name of the last sheet. Like this:

A1 = "Sheet1"
A2= "Sheet3"

Now I try to sum again using indirect formula:

=SUM(INDIRECT(A1&":"&A2&"!B2"))  ----> now I get #REF

Can anybody tell me where's my mistake? Thank you so much!

Are your entries in A1 and A2 spelt EXACTLY the same as the names themselves (no leading or trailing spaces in any of them)?

If your real names contain spaces or other special characters, then you will need to include apostrophes around the names in the INDIRECT function (and there is no harm in putting them in there, anyway).

Hope this helps.

Pete

Thanks Pete

So you mean:

SUM(INDIRECT("A1"&":"&"A2"&"!B2"))

Unfortunately, this doesn't work. The real sheet names have characters like "ü" and "_". But it's not even working with the above example "Sheet1", "Sheet2" that have no special characters.

hänschendampf

No, I didn't mean that - an apostrophe is a single quote ( ' ) and should be placed around sheet names in formulae if you have spaces in them.

However, I've just tried it in a mock-up and it doesn't work for me either. Perhaps INDIRECT is not able to work with multi-sheet references.

Pete

Thanks JeteMc

This works for me! But actually I don't understand anything in your formula. Would you mind writing a few words? That would be great. Thanks

Originally Posted by JeteMc

I am sure this is not a satisfactory solution...

I've suggested that approach several times in the past, so I'm sure it is satisfactory. Another advantage is that you can just drag sheet tabs into the space between the First and Last sheet (or drag them out) in order to model different scenarios, and so they don't have to be in any particular order.

Pete

@JeteMc: Thank you, this is the way I will handle things. It's not very beautiful but it'll work fine for me.

@WHER: That is a nice solution. Since other people will add Sheets from time to time, I think it's not really suitable. But I will keep that one in mind :-) Thanks

See this thread: http://www.excelforum.com/excel-form...-function.html
Adapt this formula to your layout, also see attachment.

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDEX(A6:A10;MATCH(A2;A6:A10;0)):INDEX(A6:A10;MATCH(B2;A6:A10;0))&"'!A1:A15");">0"))