Google Sheets – VLOOKUP Sum to Calculate Total Hours Next to Dates

formulasgoogle sheetsgoogle-sheets-query

I'm attempting to calculate a total number of hours based on entering 2 different dates. Each day between the dates has assigned 'hours worked'.

I'm trying to use Sumproduct or Arrayformula and Sumif, not having luck with either as of just yet… Any help appreciated 🙂

As far as i've got is selecting 2 dates and adding them together. Not everything inbetween

Best Answer

=SUMPRODUCT(QUERY({A9:B}, 
 "select Col2 
  where Col1>=date'"&TEXT(B1, "yyyy-mm-dd")&"' 
    and Col1<=date'"&TEXT(B2, "yyyy-mm-dd")&"'"))

Example

I've prepare an example file for you, containing to styles: Working Hours. The formulae are easily copied/pasted to fill in the next worker.

Style 1

enter image description here

Style 2

enter image description here

Formula Based on Cell Positions from Target Cell in Google Sheets

The command offset does what you want: =offset(D3, -1, -2) is the value 1 row above and two columns to the left of D3. So, your formula for D3 could be

 =offset(D3,-1,0) + offset(D3,0,-2) - offset(D3,0,-1)

Extended down the column, it will do the intended computations regardless of row insertions.

Best Answer

Related Solutions

Google-sheets – Referencing cells above and to the left in script

Example

Style 1

Style 2

Formula Based on Cell Positions from Target Cell in Google Sheets

Related Topic