Hello everyone,
I've been racking my brain on this question for over a week and have tried countless attempts to solve this, but am still spinning my wheels. Basically I'm aggregating total work history of all our staff across all our events this year, so I need to calculate how many shifts someone has worked.
The issue is that it's possible to have multiple records of the same worker per event because different supervisors may submit the same record with different ratings.
In my example, here are all the reports (records) that we received on 1 worker for the year (Table 1):
Here is the summary record (Table 2):
In the summary record (Table 2), Total shifts should be "9" and successful shifts should be "6". The current formula in the ROLLUP SUM(ARRAYUNIQUE(values)) doesn't work because if any event has the same number of shifts as another, it'll get skipped. Because there's 2 records of DF23, I can't use SUM(values) on the "# SHIFTS" field either.
I've been able to identify records that are duplicates in Table 1 using @W_Vann_Hall's base from this post, but doing this doesn't let me include the unique shift count in the total count. Does anyone have any ideas? Thank you in advance!
