COUNTIFS with running sum of values-CodePudding

We have employees in our company aligned with different departments and roles and one of our payroll tasks is to count the number of employees that work more than 10 hours on any given day.

For the example above, Lucas worked multiple roles and the count shows the results as expected since he worked 10 hours on Sunday. However, it doesn't include the count for Monday and Tuesday that total more than 10 hours. If I change his hours for one of the days on that second line, only then will the count update.

Formula

=IFERROR(
COUNTIFS(Table6[[#All],[Employee Name]],[@Employee],Table6[[#All],[MON]],">=10")  
COUNTIFS(Table6[[#All],[Employee Name]],[@Employee],Table6[[#All],[TUES]],">=10")  
COUNTIFS(Table6[[#All],[Employee Name]],[@Employee],Table6[[#All],[WED]],">=10")  
COUNTIFS(Table6[[#All],[Employee Name]],[@Employee],Table6[[#All],[THURS]],">=10")  
COUNTIFS(Table6[[#All],[Employee Name]],[@Employee],Table6[[#All],[FRI]],">=10")  
COUNTIFS(Table6[[#All],[Employee Name]],[@Employee],Table6[[#All],[SAT]],">=10")  
COUNTIFS(Table6[[#All],[Employee Name]],[@Employee],Table6[[#All],[SUN]],">=10"), "-")

How do I update my formula to keep a running sum of the individual days and not just the individual cell?

Edit:

The expected result for Lucas in cell P7 is 3 since he worked 10 hours on Monday, 10 hours on Tuesday and 10 hours on Sunday. It should have a count of 1 for any day where he worked 10 or more hours.

CodePudding user response：

If you have Excel 365 you can use this formula:

=REDUCE(0,
       BYCOL(
          FILTER(Table6[[Mon]:[Sun]],Table6[Employee Name]=A7),
          LAMBDA(d,SUM(d))
         ),
       LAMBDA(cnt,d,IF(d>=10,cnt 1,cnt))
       )

The inner part BYCOL(FILTER retrieves the rows for the employee and then sums them per day.

Due to how spill-arrays work, we cant use COUNTIF on that result :-(

Therefore REDUCE checks the result and counts the values >= 10

CodePudding user response：

Give a try on below formula as per my screenshot.

=SUM(--(BYCOL(($E$5:$J$8)*($B$5:$B$8=L5),LAMBDA(x,SUM(x)))>=10))