Hi, I’m new-ish and can’t figure out what seems like it should be a relatively simple problem.
I am using a table to track attendance in a 10-week training program. I created a column for each week/day with a drop-down of selections for our trainer to select from when taking attendance. There are corresponding hidden columns for each day with formulas that convert the drop-down selections to codes used to populate daily attendance records for each individual trainee (in a Page block).
I need to create an at-a-glance attendance summary record for the whole class that sums up excused absences, unexcused absences, and partial absences, per person.
I started by trying to create a column that would count all the instances of Excused Absences per record using a formula. I have tried the FIND(“Excused”, W#D#) formula - which works if I’m only looking at one field. But I can’t figure out how to expand the range being searched so it looks across all the days/columns. I tried listing them all individually like: FIND(“Excused”, (W1D1, W1D2, W1D3, W1D4, W1D5)), but it says there’s a problem with the formula and won’t save.
I tried FIND(“Excused”, OR(W1D1, W1D2, W1D3, W1D4, W1D5)) and FIND(“Excused”, AND(W1D1, W1D2, W1D3, W1D4, W1D5)) just for the heck of it but neither of those worked either.
I’ve also tried creating other Blocks that would calculate all of the instances of Excused Absences etc. per person, but I still can’t figure out how to get it to sum up the instances of something across a record and multiple fields. I’m open to other solutions… especially because it doesn’t seem like this ought to be so hard. So please tell me if you have better ideas.
The table looks like this right now (with two of the coded columns visible):