The user is working on a college's attendance registers and, up to now, have had no problems with the formulae to calculate attendance figures (absent, authorised absence, present, etc.).
The user has now been asked to include a column to indicate the number of times a student has been late. This means that the cell will have the date of the class, but include a comment that simply says 'LATE'. The user needs a column that just counts the number of time a 'LATE' comment has been included.
This would be easy if only the late cells had a comment, but the authorised absence cells also include a comment with the reason they have been given the absence. The user has worked out how to 'CountComments' (thanks to this forum) but the user only needs the cumulative figure for LATE.
Try this macro. It should place the number of "lates" in column AL.
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
|How to count and delete duplicate entries over multiple columns
|How to delete empty rows with counter
|How to count cells in a dynamic range matching two criteria given in table headers
|How to count blanks if 1 of 2 conditions are met