Sum + Indirect

[Milan Buchta]

I have the following problem:

I have an Excel File which has 12 sheets (Jan, Feb, Mar….. ,Dec) Thes
sheets contain
data e.g. on budgeted sales. I want to create a summary sheet whic
would calculate cumulative figures for a defined period e.g. Jan-Mar o
Jan-Jun

I want to define the period by entering a name of the last month into
special cell
So e.g. if I enter “Aug”, the period Jan-Aug will be then covered

=SUM(Jan:Aug!C4)

I tried to use the INDIRECT function, but the examples I have seen onl
dealt with
the situation where you sum a number of cells in 1 Sheet. My problem i
I want to sum
1 cell in a number of Sheets (based on the defined range).

Does anybody have an idea how to solve this problem ?

Thank you very much for your help

 

[Guest]

Hi
fi all your sheets are named in the style 'Jan', 'Feb', etc. try the
following formula on your summary sheet. Assumption: cell A1 contains the
number of the last month and you want to sum cell A1 on each of the other
sheets. Adapt to your needs:
=SUMPRODUCT(SUMIF(INDIRECT("'" & TEXT(DATE(2004,ROW(INDIRECT("1:" &
A2)),1),"MMM") &"'!A1"),"<>0"))