Index Formula Help

Topic

New Reply

I am trying to add a total number of sales from January to a specific month. This specific month would be referenced in a cell say B4. If B4 changes (July to August) the formula would now total Jan through Aug. If B4 changed to DEC then the formula would change to sum Jan to Dec.

I am new to the index function and feel this would be my answer but I am stuck.

I have attached a sample file that gives the general idea.

Any help is greatly appreciated

JG

Reply | Quote

Replies

=SUM(OFFSET($A$2,0,0,1,MATCH($B$4,$A$1:$L$1,0)))

Match() returns the column no in which the month in cell B4 is found. Offset() returns a 1 by N reference starting at A2 and ending at the desired month. Sum() adds up the values.

You might want to extend that to check Match() doesn’t return an error, in case someone enters an invalid date, or you could use data validation to make B4 a drop down list of the months in A1-A12.

OK?

Reply | Quote

Since I prefer INDEX as it is only semi-volatile, I would use:
=SUM(A2:INDEX(A2:L2,MATCH(B4,$A$1:$L$1,0)))

Reply | Quote