CODEWITHSUNDEEP
google-sheetsformula
Brian
Brian

Reputation: 21

How to count # lates per student name?

sample attendance (google) sheet

I want count the number of total lates per student name on this sample sheet, e.g, how many lates Mary Love has (actual sheet has over 30,000 rows, 10 columns). If possible, this count needs to change as students have additional lates. I'd really appreciate any help someone might be able to provide. I thank you very much in advance.

Upvotes: 0

Views: 139

Answers (3)

Kevin
Kevin

Reputation: 2631

You can use the COUNTIFS function to get what you are looking for. This formula will count all matches for multiple criteria ranges and criteria (e.g. student name & late).

This is the syntax for the formula:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

If you put Mary Love in cell I2, you could then put this in J2.

=countifs(B:B,"=late", A:A,I2)

Then as more rows are added it will automatically update the "lates". I would also suggest using named ranges, then you could replace B:B and A:A with the named range.

I would suggest using the =query function as documented below. If that doesn't meet your needs you could modify the above:

=if (countifs(B:B,"=Late", A:A,I2, D:D, ">="&N2) >= 3, "True", "False")

This will put "True" in the column if there are 3 or more lates, false if not. This will require you put the date you want to check against in column N2. Change N2 above if you want to use a different column.

Upvotes: 0

JPV
JPV

Reputation: 27282

In addition to previous post, a QUERY() function also seems a good option as it can output a 2D-array, containing the names and the counts in one formula (no need to drag down). As an example, try:

=query(A:G, "select A, count(B) where B ='Late' group by A ", 1)

If you want to limit the result tho those who had more then 3 late's, you can do:

=query(query(A:G, "select A, count(B) where B ='Late' group by A ", 1), "where Col2 > 3")

Also further filtering with date can be done very easily. However I think that may require some 'cleaning up' of the current data: I noticed a lot of different date formats in col D... :-)

Upvotes: 1

pnuts
pnuts

Reputation: 59485

For counting each of several students and to allow greater versatility I suggest a pivot table with Student Name for Rows, Late for Values with Summarise by: COUNTA and Late for Filter with Show: Late.

Upvotes: 0

Related Questions