CREATING A SUM RANGE FROM DROPDOWN LIST
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
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!