Dynamic sum formula for summing between two dynamic columns

coffeeHere is a quick but really useful coffee break dynamic sum formula tip.

I used this handy INDEX + MATCH based formula the other day and thought it was a good one to share…

Say you have this typical accounting situation where you want to give the user the flexibility to choose the 2 ‘From’ and ‘To’ columns they want to sum – in this case between Mar and Nov…

 

 

=SUM(INDEX($E$2:$P$2,1,MATCH($B$6,$E$1:$P$1,0)):INDEX($E$2:$P$2,1,MATCH($C$6,$E$1:$P$1,0)))

So the INDEX formula, perhaps my favourite formula, easily provides SUM with the From and To cell co-ordinates.  B6 and C6 are data validation dropdown lists linked to the range E1:P1.  Its almost too easy to do such a useful thing!  Note you have to ensure when choosing the columns in B6 and C6 that B6 is the left column and C6 is to it’s right.  We could write some validation rules to prevent this or error trap this in the formula if users were prone to do this, but then it wouldn’t be a quick tip.