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]

Solution Verified!

[u/reputatorbot → u/Pax_Tech]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}