Auto cell range calculations

[arunsharm82]

Hi,

I have a problem that I think can only be solved using VBA, correct me
if I'm wrong.

I have a spreadsheet that is used for resource estimations at work.

I have already managed to make the spreadsheet create a table based on
user defined preferences, but by using formulas instead of VBA.

I now need to select ranges and calculate them according on what is in
a specific box.

For example it plot a table for data input, with years and quarters
underneath. They carry on horizontally until the user defined end
date.

I need a way of calculating the totals for each year.

Is there any way of doing that? I.e. if the year is 2004, calculate the
sum of cells ... etc.

I have attached a file for reference.

Thanks.

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=417057

 

[Bob Phillips]

Try a formula like this

=SUMPRODUCT((A1:M1=2004)*(A3:M26))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sorry, this might be more meaningful

=SUMPRODUCT((C29:O29=2004)*(C31:O54))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Try a formula like this

=SUMPRODUCT((A1:M1=2004)*(A3:M26))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)