# Google Docs How To

## Google Spreadsheet

## How to sum values falling between two dates

`=ArrayFormula(sum(filter(J22:BM22,J$2:BM$2>=E$2,J$2:BM$2<F$2)))`

## How to show day for monday of Nth week

- B2 is the start date
- S1 is the number of the week from the start date

`=$B2-weekday($B2)+S1*7+2`

## How to calculate a scale from 0 to 1 based on a number of working days in a week, that fall between a start date and an end date

- E2 is the range start
- T2 contains monday of the next week's range
- Financials!... range is the list of holidays
- F2 is the range end
- S2 is monday of this week

`min(max(networkdays($E$2+1,T$2,Financials!$D$119:$L$119)/5,0),1)*max(min(networkdays(S$2,$F$2,Financials!$D$119:$L$119)/5,1),0)`

## How to get heading for a cell that shows under it, filtering by start and end dates

The heading is identified by "Lead:" in column C. Look up by A's cell content.`=IF(AK2=0,"",INDEX(Projects!A:A,SUMPRODUCT(MAX((IF(ISNA(INDIRECT("'Projects'!C1:C"&AK2)),0,INDIRECT("'Projects'!C1:C"&AK2)="Lead:"))*ROW(INDIRECT("'Projects'!C1:C"&AK2))))))`

Where AK contains the following`=ArrayFormula(min((if((A2=Projects!A:A)*(H2=Projects!E:E),ROW(Projects!A:A)))))`

Where A2 is the lookup criteria, H2 contains the auto calculated project start date:`=ArrayFormula(if(or(min(if(A2=Projects!A:A,Projects!E:E))=0,min(if(A2=Projects!A:A,Projects!F:F))<now()),"",min(if(A5=Projects!A:A,Projects!E:E))))`

Projects tab F and E are the lookup criteria start and end dates respectively

## How to calculate range based upon some criteria

- We want the sum of a range. OFFSET provides a range, based upon a starting point (optional offset in x and y) and then some dimensions.
- A1 is the reference corner, 0 and 0 are the offsets (we want to stay in the A column). the 1 at the end means 'only the A column'.
- the 'height' parameter of the OFFSET function is calculated by row of empy cell - ROW(A1) (which is the starting cell)
- INDEX(ROW(A1:A), MATCH(TRUE, ISBLANK(A1:A), 0)) calculates the rownumber of the first empty cell below A1.

`=SUM(OFFSET(A1,0,0,INDEX(ROW(A1:A),MATCH(TRUE,ISBLANK(A1:A),0))-ROW(A1),1))`

@TechnicalNotes @Google