Google-sheets – Shortening this Formula, and Future Proofing it

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I am creating a Google Sheet to use as an employee schedule. On this sheet, I have Cells which will contain the Total number of Hours worked per day by all employees.

At the moment the formula I have set up for these Cells are as follows:

=(IF(ISDATE(D16), D16)-IF(ISDATE(C16), C16))+(IF(ISDATE(D17),D17)-IF(ISDATE(C17), C17))...etc

The reason for the IF(ISDATE) checks are because the cells on the schedule contain an X value if the employee isn't available on those days, or TIME OFF if the employee has booked the time off.

For these equations as well, Column C is the Clock In time, and Column D is the Clock Out time.

My issue with this at the moment is if I add a new row, from hiring a new employee, for example, I need to manually go into the cell and add the Row values to it.

For example, if I added a new row to what I wrote above and it created Row 18, I'd need to manually add:

+(IF(ISDATE(D18), D18)-IF(ISDATE(C18), C18))

to the formula. This can be tedious and can make it difficult to check that everything is being added correctly.

Is there a way for me to set up this formula as some sort of range as a way to future proof what I am trying to do?

EDIT: Here is a link to the Google Sheet

Best Answer

Static:

=ARRAYFORMULA(SUM(QUERY(IF(ISNUMBER(
 INDIRECT("E16:F"&ROW()-1)), 
 INDIRECT("E16:F"&ROW()-1), ), 
 "select Col2-Col1")))

0

Dynamic:

paste in C31 cell and drag to the right:

=ARRAYFORMULA(SUM(QUERY(IF(ISNUMBER(
 INDIRECT(ADDRESS(16, COLUMN(), 4)&":"&ADDRESS(ROW()-1, COLUMN()+1, 4))), 
 INDIRECT(ADDRESS(16, COLUMN(), 4)&":"&ADDRESS(ROW()-1, COLUMN()+1, 4)), ), 
 "select Col2-Col1")))

0

Related Topic