Google Sheets Formula for Conditionally Counting Alpha Characters

Question

I am looking for a formula that can perform the "COUNT" equivalent of "SUMIF". I have in 'Sheet A' running records of attendance, with column A as "Last Name," column B is "First Name," and column C is "Attendance." The attendance column has values of "P, A, L" for present, absent, or late (respectively). The sheet is automatically updated each day, as new data for the day's attendance are appended at the bottom of the sheet.

In 'Sheet B' I have each student's name, matching the syntax in the above sheet ("Last Name" "First Name"). In this sheet, I want to be able to count the number of instances of each, "P", "A", and "L".

So...I want to be able to count in Sheet A the number of times a student has a "A" in the attendance column, conditionally by student name. I know that with SUMIF you can sum a range conditionally. COUNTIF does not work to appropriately filter the values by the student name. I was not able to get DCOUNTA to work either.

Open to any suggestions, no matter how complex.

Answer 1

For anyone curious, I came up with my own crude solution.

I created a new sheet for each: Present, Absent Late. Within each sheet I ran a query: =QUERY({'Imported Data'!A:C}, "select * where Col3 = 'P'"). This query returned every record where an individual was marked "P". Repeat for "A" and "L" on their respective sheets.

In my main sheet, which records count totals, I used the COUNTIF: =COUNTIF(Present!D:D,C2). I had a small problem to work around in this, as I had my data imported with a "Last Name" and "First Name" column, but could not COUNTIF across two columns. So, I created an ARRAYFORMULA in each Present/Absent/Late sheet. This concatenated the name values, so I could search against that singular value in my main sheet. This was present in D:1 of Present/Absent/Late: =Arrayformula(A:A&", "&B:B).

A little duplication and I was able to create my own, automatically updated, attendance tracker.

Answer 2

You seem to have gone to a great deal of effort to work around a problem that does not exist. In general, where SUMIF works for adding then a very similar COUNTIF should work for counting. Because in most groups of modest size neither first names nor surnames are likely to be unique (even if the combinations are likely to be) it is generally a good idea to assign IDs to people. Concatenating Last Name with First Name is effective but other options can be more compact.

Assuming in Sheet B you have P, A and L respectively in C1:E1 (a unique set of Last Name in ColumnA and First Name in ColumnB) then in C2 the following may be adequate if copied across to E2 and C2:E2 down to suit:

=COUNTIFS('Sheet A'!$A:$A,$A2,'Sheet A'!$B:$B,$B2,'Sheet A'!$C:$C,C$1)

Sheets Imported Data and Present seem irrelevant.