Logical Problem Parsing Data
I'm having a problem conceiving of a solution to this problem and I'm hoping someone out there can solve this one. I am trying to display attendance data (in this case it's weekly, although that's not really relevant). My data looks something like this:
Date | Event ID | Attendee | Headcount |
11/5/2020 | 1405 | Mike | 5 |
11/5/2020 | 1405 | Jane | 5 |
11/5/2020 | 1405 | Tom | 5 |
11/5/2020 | 1405 | Sue | 5 |
11/5/2020 | 7988 | Bill | 0 |
11/5/2020 | 7988 | Laura | 0 |
11/5/2020 | 7988 | Ed | 0 |
The way the app works is that individual attendance is logged per user, but then there is a generic "headcount" option for the leader to use for anyone who shows up that isn't in the system. So, for Event 1405, there were a total of 9 attendees (4 individuals recorded as attendees - Mike, Jane, Tom, and Sue; plus 5 more). Event 7988 has 3 individuals, and no additional people.
Unfortunately, the data exports with the headcount set for each individual at the event. So I can't just tally the headcount column. Basically, I want to use the headcount for only one of the attendees at each event, but not the others. Is there a formula (or anything else) within Zoho that can achieve this? Thanks for anyone who can help!
Michael