In need of formula to add multiple durations together from one sheet into one cell on another sheet

I have a list of hundreds (thousands) of rows of data, most of which have one duration per unique identifier, but on occasion, there are multiple durations (usually never more than 6) for the same identifier. I need a formula which will compare the identifier on Sheet A to the identifiers on Sheet B and either:

  1. If only one value exists, copy that value to Sheet A's matching duration cell.
  2. If multiple duration values exist, copy the summation of those to Sheet A's matching duration cell.

Sheet A which the summation of durations will be input:

Sheet B where the RAW data exists and one duration needs to be put into Sheet A

Example: 20240301_N12 on sheet A would be 5.4 (4.8667 + 0.5333)

What formula can help this?


Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    =SUM(COLLECT({Duration}, {Job Number}, [Job Number]@row))

    This would go into your Duration column in Sheet A. You will need to setup {Duration} and {Job Number} as cross sheet references pointing at the correct columns in Sheet B. I am guessing "Job Number" is the column heading in Sheet A, but if not, you will need to adjust that as well.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!