# Getting Sum of Multi-Value Dropdown list with Reference list

✭✭✭
edited 08/07/24

Good morning

I would like to be able to sum the total values of distance and time on my master sheet from the values on my reference sheets. Generally there should be no more than 6-8 combination of values per trip.

The reference sheet below:

• ✭✭✭✭✭✭

Try this:
=SUMIFS({Distance}, {Destination}, HAS(Destination@row, @cell))

• ✭✭✭

I ended up finding a solution for the minutes by extracting the hours and minutes seperately using the following formulas:

Hours
`=INT([ETA Minutes]@row / 60)`

For Minutes
`=IF(([ETA Minutes]@row - Hours@row * 60) < 10, "0" + ([ETA Minutes]@row - Hours@row * 60), ([ETA Minutes]@row - Hours@row * 60))`

Thanks again for all the help!

• ✭✭✭
edited 08/07/24

I did find some success with replacing the comma with a line break Char(10) but it does not seem to function for the last column well. I seem to be pulling doubles on the 4th destination.

Destination 1
`=IF(ISERROR(FIND(CHAR(10), Destination@row)), Destination@row, LEFT(Destination@row, FIND(CHAR(10), Destination@row) - 1))`

Destination2
```=IF(ISERROR(FIND(CHAR(10), Destination@row, 1)), "", MID(Destination@row, FIND(CHAR(10), Destination@row) + 1, IFERROR(FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1) - FIND(CHAR(10), Destination@row) - 1, LEN(Destination@row) - FIND(CHAR(10), Destination@row)) ) )```

Destination3
```=IF(ISERROR(FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1)), "", MID(Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1) + 1, IFERROR(FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1) + 1) - FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1) - 1, LEN(Destination@row) - FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1)) ) )```

Destination4
```=IF(ISERROR(FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1) + 1)), "", MID(Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1)) + 1, IFERROR(FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1) + 1) + 1) - FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1)) - 1, LEN(Destination@row) - FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row, FIND(CHAR(10), Destination@row) + 1))) ) )```

• ✭✭✭

I also tried the following formulas in Trip/ETA

Trip Miles

`=SUMIFS({DistanceRef}, {DestinationRef}, CONTAINS(Destination@row, {DestinationRef}))`

Trip Duration

`=SUMIFS({ETARef}, {DestinationRef}, CONTAINS(Destination@row, {DestinationRef}))`

• ✭✭✭✭✭✭

Try this:
=SUMIFS({Distance}, {Destination}, HAS(Destination@row, @cell))

• ✭✭✭

Sir… the circles I have been putting myself through. Thank you for your help!

• ✭✭✭
edited 08/07/24

One more question for you, now that I'm able to sum the duration.
I'm using the following formula to breakdown the total minute duration into HR : Min format such as 00:00

`=INT([ETA Minutes]@row / 60) + ":" + MOD([ETA Minutes]@row, 60)`

However, I'm getting a value return of 2 minutes as 7:2 rather than 7:02. This would indicate 7 hours and 02 minutes. Am I using the wrong formula?

`=INT([ETA Minutes]@row / 60) + ":" + ``TEXT``(MOD([ETA Minutes]@row, 60), "00")`

Forgot TEXT does not work in Smartsheet.

• ✭✭✭

I ended up finding a solution for the minutes by extracting the hours and minutes seperately using the following formulas:

Hours
`=INT([ETA Minutes]@row / 60)`

For Minutes
`=IF(([ETA Minutes]@row - Hours@row * 60) < 10, "0" + ([ETA Minutes]@row - Hours@row * 60), ([ETA Minutes]@row - Hours@row * 60))`

Thanks again for all the help!

• ✭✭✭✭✭✭

You can do hours:minutes in a single cell:

=ROUNDDOWN([ETA Minutes]@row / 60) + ":" + RIGHT("0" + ([ETA Minutes]@row - 60 * ROUNDDOWN([ETA Minutes]@row / 60)), 2)

• ✭✭✭