A dynamic sum formula - Excel

1
votes

Guys I am trying to write a dynamic sum formula where the array range starts from a cut-off date ( this date changes every month ).

I have the periods in the top first row 201801 in A2, 201802 in B2 etc. and starting from A3 I have the sales' figures.

e.g. the cut-off date is 201806, so I need my sum formula to be =sum($F3:L3)

what I was trying to do is =sum(ADDRESS(3,MATCH($B$1,$A$2:$L$2,0),3):L3)

ADDRESS(3,MATCH($B$1,$A$2:$L$2,0),3) returns $F3 which does not work in the sum formula!

1
excel
You just need and INDIRECT to convert to a range ref =SUM(INDIRECT(ADDRESS(3,MATCH($B$1,$A$2:$L$2,0),3) &":L3")). - SJR
Thank you very much - Nasser Abdulrahman

1 Answers

2
votes

INDIRECT is volatile so may not be the best function to use.

I take it that L3 is static, and you're looking to sum from the lookup value to L3.

  • MATCH(201806,$2:$2,0) will return the column number that 201806 first appears in.
  • INDEX($3:$3,,6):$L$3 will return a reference to F$3:$L$3 (F being the sixth column - replace 6 with the MATCH function).
  • SUM(F$3:$L$3) adds it all up. Replace F$3:$L$3 with the INDEX.

The final formula would be: 

=SUM(INDEX($3:$3,,MATCH(201806,$2:$2,0)):$L$3)

Replace 201806 with a range reference.

If the end date is variable you can use another INDEX function to find it.
This finds the last date using the MAX function: 

=SUM(INDEX($3:$3,,MATCH(201806,$2:$2,0)):INDEX($3:$3,,MATCH(MAX($2:$2),$2:$2,0)))

This adds from June to December: 

=SUM(INDEX($3:$3,,MATCH(201806,$2:$2,0)):INDEX($3:$3,,MATCH(201812,$2:$2,0)))