Getting Sum of Multi-Value Dropdown list with Reference list

Jimrny

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:

Paul Newcome

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

Jimrny

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!

Jimrny

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)))
)
)

Jimrny

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}))

Paul Newcome

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

Jimrny

https://community.smartsheet.com/discussion/comment/448206#Comment_448206

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

Jimrny

@Paul Newcome

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.

Jimrny

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!

Nick Korna

@Jimrny,

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)

Jimrny

https://community.smartsheet.com/discussion/comment/448308#Comment_448308

Thanks this is also helpful!

Paul Newcome

Happy to help. 👍️