Sum + Indirect

M

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
 
F

Frank Kabel

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"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top