SOLVED

New Contributor

# 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 each month, shifts (D1/D2/N1/N2) and number of SL absences. Can an array be setup to automatically sum/calculate the total SL absences for each shift type by day of week, i.e. # absences for a D1 shift on Sundays, # absences for a D2 shift on a Sunday, # absences for a N1 shift on a Sunday, etc.

6 Replies

# Re: Create formula for number of absences on day or night shifts by days of week

That could be like

=SUMPRODUCT((\$C\$4:\$AR\$27=G\$32)*(\$C\$3:\$AR\$3="Su"))

# Re: Create formula for number of absences on day or night shifts by days of week

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.

# Re: Create formula for number of absences on day or night shifts by days of week

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?

best response confirmed by BigAl-MFS (New Contributor)
Solution

# Re: Create formula for number of absences on day or night shifts by days of week

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

# Re: Create formula for number of absences on day or night shifts by days of week

Many thanks, perfect!

# Re: Create formula for number of absences on day or night shifts by days of week

@BigAl-MFS , you are welcome