I have a complex employee schedule spanning one year with 25 individuals in a Google Sheets format. Each individual may have more than one duty on a given day and these are delimited by commas currently. Here is a shortened and simplified sample as I cannot attach the original sheet to respect anonymity of coworkers:
| 1/1/2022 | 1/2/2022 | 1/3/2022 | 1/4/2022 | 1/5/2022 | 1/6/2022 | 1/7/2022 | 1/8/2022 | 1/9/2022 | 1/10/2022 | 1/11/2022 | 1/12/2022 | 1/13/2022 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Person 1 | Office, , | Lab, , | Office, , | Rounder, , | Rounder, Night Call , | Back Up Call, Rounder, | Back Up Call, Rounder, | Rounder, , | Rounder, , | Rounder, , | Office, , | ||
| Person 2 | Rounder, , | Rounder, , | Rounder, , | Rounder, , | Rounder, , | Office, , | Office, , | , , | , , | Office, , | Office, , | Office, , | Office, , |
| Person 3 | Back Up Call | Night Call, Rounder, | Back Up Call, Rounder, | Office,, | Office, , | Lab, , | Lab, , | , , | , , | Office, , | Lab, , | Office, , | Rounder, , |
| Person 4 | , | , | Vacation, | Vacation, | Office, | Rounder, | Rounder, | Rounder, | Night Call, Rounder | Rounder, | Rounder, | Rounder, | Office, |
| Person 5 | , | , | Vacation, | Back Up Call, | Night Call, , | Back Up Call, | Vacation, | , | , | Vacation, | Vacation, | Vacation, | Vacation, |
To ensure fairness, I need to quantify how often certain events occur. I was helped greatly by member 
Display table
Assuming the first tab is named Sheet1, paste this formula in another sheet
=ArrayFormula({ "Persons", REGEXREPLACE(Sheet1!A2:C2, " \| ", CHAR(10)); Sheet1!E3:E, Sheet1!A3:C})
Notes
Keep an eye on the range $F3:$500 and $E$3:$E$500 If you have less or more than 500, adjust accordingly. I set it to 500 to avoid missing references.
Resources
| **Streaks of** | | | | | | | | | | | | | | | | | | | | | | | | |
|:------------------------------------------:|:------------------------------------------:|:------------------------------------------:|:-:|----------|--------------|----------------------|------------------------|---------------|---------------|---------------|-----------------------|------------------------|------------------------|------------|------------|------------|------------|--------------|------------|--------------|---|--------------|------------|--------------|
| Back Up Call \| Back Up Call \| Night Call | Back Up Call \| Night Call \| Back Up Call | Night Call \| Back Up Call \| Back Up Call | | 1/1/2022 | 1/2/2022 | 1/3/2022 | 1/4/2022 | 1/5/2022 | 1/6/2022 | 1/7/2022 | 1/8/2022 | 1/9/2022 | 1/10/2022 | 1/11/2022 | 1/12/2022 | 1/13/2022 | | | | | | | | |
| **0[Formula Here]** | 0 | 1 | | Person 1 | | | Office, , | Lab, , | Office, , | Rounder, , | Rounder, Night Call , | Back Up Call, Rounder, | Back Up Call, Rounder, | Rounder, , | Rounder, , | Rounder, , | Office, , | | | | | | | |
| 0 | 0 | 0 | | Person 2 | Rounder, , | Rounder, , | Rounder, , | Rounder, , | Rounder, , | Office, , | Office, , | , , | , , | Office, , | Office, , | Office, , | Office, , | | | | | | | |
| 0 | 1 | 0 | | Person 3 | Back Up Call | Night Call, Rounder, | Back Up Call, Rounder, | Office,, | Office, , | Lab, , | Lab, , | , , | , , | Office, , | Lab, , | Office, , | Rounder, , | | | | | | | |
| 0 | 0 | 0 | | Person 4 | , | , | Vacation, | Vacation, | Office, | Rounder, | Rounder, | Rounder, | Night Call, Rounder | Rounder, | Rounder, | Rounder, | Office, | | | | | | | |
| 0 | 3 | 0 | | Person 5 | , | , | Vacation, | Back Up Call, | Night Call, , | Back Up Call, | Vacation, | , | , | Vacation, | Vacation, | Vacation, | Vacation, | Back Up Call | Night Call | Back Up Call | | Back Up Call | Night Call | Back Up Call |
Display table
| **[Formula here] Persons** | **Back Up Call Back Up Call Night Call** | **Back Up Call Night Call Back Up Call** | **Night Call Back Up Call Back Up Call** |
|----------------------------|------------------------------------------|------------------------------------------|------------------------------------------|
| Person 1 | 0 | 0 | 1 |
| Person 2 | 0 | 0 | 0 |
| Person 3 | 0 | 1 | 0 |
| Person 4 | 0 | 0 | 0 |
| Person 5 | 0 | 3 | 0 |