# CREATING A SUM RANGE FROM DROPDOWN LIST

Options
✭✭✭✭✭

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.

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

thank you

• ✭✭✭✭✭
Options

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)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!