Forum Discussion
Solved
Create formula for number of absences on day or night shifts by days of week
Trying to develop a formula for a sheet where the data represents month at a time sick leave occurrences by day shift or night shift by day of week. Data is extracted from HR system showing date for ...
Oops, I missed that.
Formula could be
=SUMPRODUCT(IF(NOT(ISNUMBER($C$6:$AR$27)),,$C$6:$AR$27)*($C$5:$AR$26=G$32)*($C$3:$AR$3="Su"))if you are on Excel without supporting Dynamic Arrays, use it as array formula, i.e. with Ctrl+Shift+Enter
Is there a way of SUM'ing the row below the applicable cell using some sort of 'row+1' for those cells found which meet the criteria?
Oops, I missed that.
Formula could be
=SUMPRODUCT(IF(NOT(ISNUMBER($C$6:$AR$27)),,$C$6:$AR$27)*($C$5:$AR$26=G$32)*($C$3:$AR$3="Su"))if you are on Excel without supporting Dynamic Arrays, use it as array formula, i.e. with Ctrl+Shift+Enter
Many thanks, perfect!
Hi Sergei,
Thank-you, that gives the number of occurrences of Shifts for Day of Week but atm does not add (SUM) the actual occurrences of sick leave absences (rows labelled # SL), which are on the next row under each of the SHIFT rows within the table. Any further help would be appreciated.
