Ability to count the number of cells containing a comment that contains the word LATE

  • This sounds easy but I'm finding the task incredibly difficult.


    I am working on our college's attendance registers and, up to now, have had no problems with the formulae to calculate attendance figures (absent, authorised absence, present, etc.). I have 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'. I need 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. I've worked out how to 'CountComments' (thanks to this forum) but I only need the cumulative figure for LATE.


    I'm pulling my hair out! Can anyone help?

  • Could you post a copy of your file? De-sensitize the data if necessary. Explain in detail what you want to do using a few examples of your data and referring to specific cells, rows, columns and worksheets.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thanks Mumps – I’ve attached a copy of the register system I’ve been working on, but removed all of the names of the individuals – leaving the current ‘marking scheme’ in place along with the formulae and results.

    So, basically, the formulae are in place to calculate attendance, possible attendance, number of sessions, etc. as can be seen from the columns AE-AK; this is based on the fact that dates are used to confirm attendance, ‘x’ for unauthorised absences and ‘AA’ for authorised absences. When an individual as an ‘AA’, a comment is inserted to inform us of their reasons.

    When an individual is late, the date of the class (to confirm attendance) is inserted, but so is a comment to indicate lateness (at the moment the comment itself varies, but the idea is regulate this – eg. ‘LATE 10 mins’ or LATE 25 mins’.

    The academic staff and principals have suggested that a column be added to show the number of times a student has been late, but maintain the current way of recording attendance.

    I would like to have a column that simply shows the number of times a comment with the word ‘LATE’ has been inserted on each row. As there are other comments inserted for those who have been absent, I can’t just count the number of comments.

    I have tried to create a formula without success, and my knowledge of macros is incredibly limited.

  • Try this macro. It should place the number of "lates" in column AL.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try this macro. It should place the number of "lates" in column AL.


    Thank you SOOOOOOOO much - that works brilliantly! I now plan to grow back all the hair I had pulled out!

  • You are very welcome. :) You shouldn't pull your hair out. Unfortunately, eventually it falls out on its own. ;;)

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!