Forum Discussion
Need formula to sum the total duration of hours spent by an employee in a training
With Excel 2016, dynamic arrays and the newer text functions are not available. Unfortunately, Excel 2016 does not do elegance!
It really depends on how much data you have but if I were you I'd fix the data up a bit first before using any formulas.
Then use the ol' workhorse SUMPRODUCT:
=SUMPRODUCT({3600,60,1},E7:G7)/3600
Hello Patrik, Very Good Morning Thank you so much for providing the formula in the excel sheet.
The no of days count is still not solved as if you see EMP Manikanta in Main base data tab the count should come 7 as 11/22/23 is 3 times and should count as only 1 day and duration should be summed for all 9 columns.
Also i tested by adding a new employee from row # 16 to 19 in Main base data and tested no of days and duration it is not working ( Attached sheet for your reference). Please help
I've updated the named items in the workbook to be dynamic.
To deal with the duplicates, I recommend using Remove Duplicates. Without access to UNIQUE, remove dupes with a formula is a bit of a chore.