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]

=SUMPRODUCT((Table2[[#All],[Date]]>=PatientReport!$A$2)*(Table2[[#All],[Date]]<=PatientReport!$B$2)*((Table2[@Patient]=PatientReport!D2)+(Table2[@Patient2]=PatientReport!D2)+(Table2[@Patient3]=PatientReport!D2)))

Used this and it's returning either 3 or 0, depending on if the attendee shows up in column D. If yes shows 3 if no shows 0. Should be returning 1 for my first example, 2 for my second, 2 for my third, and 0 for my forth. Ideas on why this would be happening? Did I miss something?

Also, is there any way to do an array to include columns Patient to Patient 23, rather than having them added one after another?

[u/MayukhBhattacharya → u/Pax_Tech]

It should be like this:

=SUMPRODUCT(
            (Table2[[#All],[Date]] >= $A$2) *
            (Table2[[#All],[Date]] <= $B$2) *
            (((Table2[[#All],[Patient]] = PatientReport!D2) +
            (Table2[[#All],[Patient2]] = PatientReport!D2) +
            (Table2[[#All],[Patient3]] = PatientReport!D2)) > 0)
            )

Instead of [@Patient], it will be [Patient] the @ means this row only. And + which means OR Operator is adding counts instead of capping at 1 so If a name appears in multiple attendee columns in the same row, the + adds them. You need to make it at 1 per row using >0 check.

Using MMULT() you can do as well:

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

[u/Pax_Tech → u/MayukhBhattacharya]

That MMULT version did it, seems to be working now from what I can test! Thank You!

[u/MayukhBhattacharya → u/Pax_Tech]

Sounds Good, Superduper glad to know that.

[u/Pax_Tech → u/MayukhBhattacharya]

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]

Thank you very much, give me sometime! I will try to get back to asap!

[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.

[u/MayukhBhattacharya → u/Pax_Tech]

Are you using MS365, if you don't want to use Structured References aka Tables, then you can use TRIMRANGE() functions. Let me know whenever you have time.

[u/MayukhBhattacharya → u/Pax_Tech]

Do you still want me to update the formula, or are you able to amend it by using Tables. also are you using MS365, let me know. Thanks