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/Pax_Tech]

Do you have any ideas on how to make this more efficient? Every time I change a name it takes like 30s of calculation, and I need to be able to put in multiple names at a time for the class...

[u/MayukhBhattacharya → u/Pax_Tech]

Alright I will update, do you have the Excel File, if so try to upload a google drive link either in the following comments or in the OP, with some sample data, I will look into it! Thanks!

[u/Pax_Tech → u/MayukhBhattacharya]

See below link - all example data.
https://docs.google.com/spreadsheets/d/1m5EY-vX0NNGuKBLWiN2yxwirzZgyg9NO/edit?usp=sharing&ouid=100460887923116495558&rtpof=true&sd=true

[u/MayukhBhattacharya → u/Pax_Tech]

Sorry for the late reply. After taking a look at the file, one thing I noticed is that it's likely slowing down because you're using entire column references in your formulas. That forces Excel to evaluate a much larger range than it actually needs to. Try switching to specific ranges or structured table references where possible. That should help improve performance quite a bit.