CODEWITHSUNDEEP
excel
Nasser Abdulrahman
Nasser Abdulrahman

Reputation: 13

A dynamic sum formula - Excel

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!

Upvotes: 1

Views: 117

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

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

Upvotes: 2

Related Questions