Count how many times 1 column AND multiple columns (using OR logic) show up

[kyledupic]

Excel file attached.

I want to know how many times a 7th grader showed up AT LEAST ONCE to the 4 dates showing (and then the same for 8th graders). It would be nice to expand the logic to how many times did they show up twice, three times, etc.

In my mind, it would be something like countif 2 conditions are satisfied:

1) 7th grader

2) There is a 1 (or less than 2)  in 3/3/23, 3/10/23, 3/17/23 OR 3/24/23.

I'm trying to use Countifs logic, but maybe I need be using something else. I like countif because it produces a 1 for me, making it easy to see the number of times it happened.

 

[#2 · Accepted solution]

@kyledupic 

=SUMPRODUCT(($B$3:$B$28=$O7)*($I$3:$I$28=P$6))

With the results from column I you can try this formula.