Adding up run times from separate tabs (duration field) to get total run time

[Kristina_Lucare]

I’m stumped on how to add up duration fields (segment run times from different tabs) to add up a total run time comprised of multiple segments.

I’m working with 3 “segment” types here.

The shortest segments, “Tab A” are linked to the medium one “Tab B” So, records from “Tab A” are added together to get a run time for some “Tab B” segments, but not all. Some “B” segments have no elements from “Tab A,” So I created a function field that adds up “Tab A” durations, along with a duration field just for segments unique to “Tab B”

Our longest, “Tab C” are made up of records form both “A” and “B”. But for some reason, the total run time for “Tab C” is not adding up!

What I want is a total from A-Unique TRT, B-Combined TRT and B-Unique TRT to add up to a total duration time in the “Total TRT” field. In the screenshot below, the first record should total 10:00, but it’s showing as 0:01.

The formula I am using is “SUM({B-Combined TRT}+{A-Unique TRT}+{B-Unique TRT})”

Is the zero in Combined TRT causing the error? I can’t figure it out.

Screen Shot 2020-11-25 at 5.16.49 PM|700x368

[Justin_Barrett · Accepted solution]

Welcome to the community, @Kristina_Lucarelli! :grinning_face_with_big_eyes: And welcome to the wonderful world of lookup fields.

Lookup fields create (and return to formula fields) arrays of values, not individual values. Because of that, they’re the source of much confusion in terms of how to use them. Long story short, you can’t add a collection of arrays in the same way that you would add a group of individual values.

On a side note, the correct way to pass things to the SUM() function is by separating the items with commas:

SUM({Field 1}, {Field 2}, {Field 3})

That’s the same as writing this:

{Field 1} + {Field 2} + {Field 3}

While that doesn’t work around the lookup field issue, we can use the SUM() function in a different way to solve this problem.

In addition to adding comma-separated items, SUM() can also add the contents an array. Knowing that, you could SUM() the array from each lookup field, and then add those sums together for the final result, like this:

SUM({B-Combined TRT}) + SUM({A-Unique TRT}) + SUM({B-Unique TRT})

That should produce the output you’re seeking.

[Kristina_Lucare]

THAT WAS IT! Thank you so much, Justin! This is going to make my life so much easier in 2021 :slightly_smiling_face:

And happy to be here haha!