CREATING A SUM RANGE FROM DROPDOWN LIST

11/29/21
Answered - Pending Review

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

Answers

  • Devin LeeDevin 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([email protected]:[email protected], $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)



Sign In or Register to comment.