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:
- If only one value exists, copy that value to Sheet A's matching duration cell.
- 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
-
=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.
Answers
-
=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.
-
I added cross sheet references to the other sheet columns and I'm only getting 0's although there is data in the other sheet. You were correct on the headers although the other has a ':'. please let me know if I'm missing something here, appreciate the help!!
-
Ignore, sorry, I just realized I mixed them up! I appreciate it and the numbers are calculating properly
-
Happy to help!
Help Article Resources
Categories
Check out the Formula Handbook template!
