CREATING A SUM RANGE FROM DROPDOWN LIST

Archie Villa

Hello everyone,

I am currently creating a dynamic sheet wherein it sums a certain range using a dropdown, but i cant figure how i could use the index and match formula.

LINK:https://app.smartsheet.com/b/publish?EQBCT=16d13980574b4a76ac8a3378452dc61d

The month1 and Month2 would be my dropdown list then it populates the green rows on column 5.

thank you

Devin Lee

Hey @Archie Villa

You need to convert the dropdown options to numbers and assign those numbers to each column. From there you can use the SUMIFS formula with the criteria of being equal to or higher than the start number and equal to or lower than the end number.

In the example below the formula is

=SUMIFS(January@row:December@row, $January$1:$December$1, >=$[Start Month]$3, $January$1:$December$1, <=$[End Month]$3)

The lookup dropdown conversion for the start and end numbers are using an INDEX/MATCH formula

Start Number

=INDEX(Number:Number, MATCH([Start Month]2, Month:Month, 0), 0)

End Number

=INDEX(Number:Number, MATCH([End Month]2, Month:Month, 0), 0)