Referencing/Adding values from multiple cells in a sheet and inputting in another

vcuellar

Hi!

I have my reference sheet (Quick Issues Form) where Issues are submitted as they happen during a shift, and they have a date, description, and delay time. There might be 3 different submissions for 3 different Issues that occurred on the same date. I have my destination sheet (Shift Notes Form), where an entry is submitted at the end of the shift as a summary. I have columns named "Issues Descriptions" and "Total Delay Time", where, as of right now, staff have had to manually copy/paste what was submitted in the different entries for that day in the Quick Issues. I want to create a formula for the "Issues Descriptions" that goes into the reference sheet (Quick Issues), checks the entries submitted on the matching date as in the entry of the destination sheet, and compiles all of the descriptions. Same as for the Total Delay Time, I want a formula that goes into the reference sheet, checks the entries submitted on the matching date as in the entry of the destination sheet, and sums all the entered delay times.

(Here attached is an example of issues submitted on the reference sheet for the date 12/13/2024)

Thank you in advance!

Jason Tarpinian

I think you'd like something like below. To join text values, you can use the JOIN function, and the COLLECT function is used to only join the values based on specific criteria (in the case below, the "Summarize Date" field I made). The CHAR(10) is a special character function for line break, so you can see the formula returns a break between each value for the date.

=JOIN(COLLECT([Issue Description]:[Issue Description], [Date Issue Occurred]:[Date Issue Occurred], [Summarize Date]@row), CHAR(10))

To join values, you can use the SUMIFS function. Same general principle as above, but we are summing values instead of concatenating them.

=SUMIFS([Length of Critical Path Delay (hours)]:[Length of Critical Path Delay (hours)], [Date Issue Occurred]:[Date Issue Occurred], [Summarize Date]@row)

*Note that for joining descriptions, you'll still have the 4,000 character limit per cell.

Jason Tarpinian

I think you'd like something like below. To join text values, you can use the JOIN function, and the COLLECT function is used to only join the values based on specific criteria (in the case below, the "Summarize Date" field I made). The CHAR(10) is a special character function for line break, so you can see the formula returns a break between each value for the date.

=JOIN(COLLECT([Issue Description]:[Issue Description], [Date Issue Occurred]:[Date Issue Occurred], [Summarize Date]@row), CHAR(10))

To join values, you can use the SUMIFS function. Same general principle as above, but we are summing values instead of concatenating them.

=SUMIFS([Length of Critical Path Delay (hours)]:[Length of Critical Path Delay (hours)], [Date Issue Occurred]:[Date Issue Occurred], [Summarize Date]@row)

*Note that for joining descriptions, you'll still have the 4,000 character limit per cell.

vcuellar

Thank you so much!