Sum Training Hours by Month, Year, and Name

ryan.radomski

I am trying to track if employees have achieved the required 4 hours of training per month requirement. I would like to visually display who has not completed this training for the month on the dashboard.

Employees log their training using a form using drop downs (so there are multiple entries for each employee). Employees are set by their names in the system so each is unique. I was able to finally pull a list of each employee without duplicates however I am trying to figure out how to sum by month and year for each employee using the unique identifier (i.e. their name).

Is there a better way to do this than pulling a "unique ID" with each? Is there just a way to complete these from the data that is coming in when a training is logged by employees and what is the best way to display who has not met the 4 hour goal on the dashboard?

Paul Newcome

To get a unique list on another sheet…

Use a manually populated text/number column called "Number". You will put 1 on row 1, 2, on row 2, so on and so forth until you have enough rows populated to support the maximum number you think your list will grow to plus a buffer just in case.

In the next column over you would use:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Names}, {Source Sheet Names}, @cell <> "")), Number@row), "")

ryan.radomski

Here is a screenshot of how the data is coming in when a training is logged

Paul Newcome

Assuming your sheet with the non-duped list is separate from your screenshot, you would use a SUMIFS along the lines of:

=SUMIFS({Hours}, {Names}, @cell = [Name Column]@row, {Dates}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2025))

ryan.radomski

Thank you. I am having issues with the non-duplicate list as new entries are submitted, with my "reference number" column using the first row with the formula as

=COUNT(DISTINCT([Employee Name]$1:[Employee Name]@row))

and each row after as

=IF(COUNT(DISTINCT([Employee Name]$1:[Employee Name]@row)) <> COUNT(DISTINCT([Employee Name]$1:[Employee Name]1)), COUNT(DISTINCT([Employee Name]$1:[Employee Name]@row)))

When new entries are submitted via the form, the numbering is off and is showing blanks, any ideas for a better way to pull a non-duplicated list?

Paul Newcome

What exactly are you wanting to accomplish with the formulas? Are you just trying to generate a count for the first entry for each [Employee Name]? How are you leveraging this after you generate the count? Are you using a separate sheet?

ryan.radomski

@Paul Newcome I simply just need to pull a unique list (removing duplicates) from the training log names as they are entered. If I have that unique list, I then can use a formula like SUMIFS to collect the tally for each month and display it on the main dashboard to easily reference who is <4 hours. The unique list is currently on a separate sheet.

These formulas may not be the best way to achieve what I am looking to do, I have been relying on previous threads from other solutions to a similar problem

Hope that is making sense

ryan.radomski

@Paul Newcome These formulas may not be the best way to achieve what I am looking to do, I have been relying on previous threads from other solutions to a similar problem

Paul Newcome

To get a unique list on another sheet…

Use a manually populated text/number column called "Number". You will put 1 on row 1, 2, on row 2, so on and so forth until you have enough rows populated to support the maximum number you think your list will grow to plus a buffer just in case.

In the next column over you would use:

=IFERROR(INDEX(DISTINCT(COLLECT({Source Sheet Names}, {Source Sheet Names}, @cell <> "")), Number@row), "")

ryan.radomski

This worked perfectly thank you!!

Paul Newcome

Happy to help. 👍️