Counting Occurrence of a Value Across a Row

[Beth_Lowder]

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):

Thank you!

[Celeste_Bancos]

You can do this with a very long formula: COUNT(SEARCH(“Excused”, W1D1), SEARCH(“Excused”, W1D2), SEARCH(“Excused”, W1D3), …) Just add a SEARCH function for every column that you’re checking.

The COUNT function tallies up all the numeric values. So if the SEARCH function finds an “Excused”, it will return a number and be counted. But if it doesn’t find anything, it will return an empty and it won’t be counted. That’s why I’m using SEARCH instead of FIND, because FIND returns a 0 if it doesn’t find anything (and since 0 is a number, it would still get included in the count).