How To Construct a SUM formula

[simonj64]

Hi,

I am looking at writing a formula to sum values of a calendar (columns in months) up to a certain period in that calendar - and would like for it to generically work without having to update the ranges in a formula each month etc.

Below is a pict of the data - for ID = A, the formula sits on the first row and needs to sum the data from the second row upto a certain point. I need to be able to copy this to multiple ID rows etc. I have been getting use to using the INDEX MATCH functions and wondered if this was a route to go. Additionally this information may be setup as a table.

Hopefully the table is readable


Current Period Mar

ID DataType Jan Feb Mar Apr May Jun Jul Sum Period
A Summary 60
A Phased 10 20 30 40 50
B Summary 6
B Phased 1 2 3 4 5 0
0

Any thoughts on how I can parametise the summation of the Phased row for an ID upto a date based on a user entry ?

Thanks in advance

Simon

 

[Andrew Poulsom]

Is this what you want?

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	ID	DataType	Jan	Feb	Mar	Apr	May	Jun	Jul	Sum Period
2	A	Summary	60							Mar
3	A	Phased	10	20	30	40	50
Sheet1

Cell Formulas
Range	Formula
C2	=SUM(C3:INDEX(C3:I3,MATCH(J2,C1:I1,FALSE)))

 

[simonj64]

Great Thanks - Just what I needed. One slight difficulty I do have is that I have to perform another similar calc but up to the end of the previous month - if as in the example above the Period is Mar (and I have this value set as a named range e.g. dReportMonth, how can I do the sum up to the end of the previous month e.g. Feb ?

Thanks once again

SJ

 

[Andrew Poulsom]

Maybe try:

=SUM(C3:INDEX(C3:I3,MATCH(J2,C1:I1,FALSE)-1))

 

[simonj64]

Thankyou very much Andrew.