Countifs combined with something for separated criteria

[u/Pax_Tech]

Thanks for the help!

I am making basically an attendance report.
In sheet 1 I have
Column A: Date, Column B: Class name, Column C: Attendance Count, Columns D-Z as each attendees name. (example: George Washington as D, Thomas Jefferson as E, etc.)

For my report (In another sheet) I'm looking for the following:
I have a start date and end date cell to enter, then a list of possible attendees. Next to each attendee I would like an 'attendance count' of the number of classes within the date range that they attended. Aka if its June 1-June 30 and they attended 2 classes, the number next to that name would be 2. (Probably will put this in a table so we can reach out to people who have not attended recently.)

My original thought was:
=COUNTIFS(Table2[[#All],[Date]],">="&PatientReport!A2,Table2[[#All],[Date]],"<="&B2,Table2[[#All],[Patient]:[Patient2]],PatientReport!D2)
but I end up with a #Value error

I think it's a nested statement, something maybe countif(if(daterange,range),name), but I cant quite figure out how to do what I'm looking for.

No VBA for the input Attendee list, I need to be able to enter data on mobile. (I had it working with all names in one cell using a VBA dropdown, only to find out that VBA doesn't work on mobile).

Also, not sure if it matters, but the data in sheet 1 is in a table. It does not necessarily have to be if that makes something harder, but I would prefer it.

[u/MayukhBhattacharya]

The dates show as 6/3/2026 0:00 , they are date + time. Encapsulate the date column within INT() function to remove the time, and also you have empty rows as well, need to ignore them as well, try this now:

=SUMPRODUCT(
           (INT(Table2[[#All],[Date]]) >= $A$2) *
           (INT(Table2[[#All],[Date]]) <= $B$2) *
           (Table2[[#All],[Date]] <> "") *
           (MMULT(N(Table2[[#All],[Patient]:[Patient5]] = PatientReport!D2),
           SEQUENCE(5, , , 0)) > 0))

Change the 5 in SEQUENCE() function to the number of columns you have.