Similarly, if SUM is called with an expression that references two or more columns. x, na.rm = TRUE ) ) ) #> # A tibble: 10 × 4 #> homeworld height mass birth_year #> #> 1 Alderaan 176. The window function returns one output row for each input row. The SUM function sums the ranges returned by OFFSET.Starwars %>% summarise ( across ( where ( is.character ), n_distinct ) ) #> # A tibble: 1 × 8 #> name hair_color skin_color eye_color sex gender homeworld species #> #> 1 87 13 31 15 5 3 49 38 starwars %>% group_by ( species ) %>% filter ( n ( ) > 1 ) %>% summarise ( across ( c ( sex, gender, homeworld ), n_distinct ) ) #> # A tibble: 9 × 4 #> species sex gender homeworld #> #> 1 Droid 1 2 3 #> 2 Gungan 1 1 1 #> 3 Human 2 2 16 #> 4 Kaminoan 2 2 1 #> # ℹ 5 more rows starwars %>% group_by ( homeworld ) %>% filter ( n ( ) > 1 ) %>% summarise ( across ( where ( is.numeric ), ~ mean (. To recap: OFFSET returns a calculation of the correct range for each week using the row logic explained above. As the formula is copied down into F4:F6, we get the final totals per week: SUM(C5:C9) // returns 220 After OFFSET is evaluated, the resulting range is delivered directly to the SUM function, which sums the values in the range and returns a final result.

The final step in the problem is just to sum the ranges provided by OFFSET. Finally, we provide width as 1, because we want a range that contains 1 column. We provide height as 5 because we want a range that contains 5 rows. To finish off the arguments for OFFSET, we provide cols as zero, because we want to stay in the same column.

Then we multiply the result by 5, this is how this snippet will evaluate in cells F4, F5, and F6: F4=(ROW()-4)*5 // returns 0 We first subtract 4 as an 'offset' because we want to create a zero-based index, for reasons made clear below. We use this fact to create the logic we need. Because the formula sits in cell F4, ROW() will return 4. ROW returns the row number for a given reference or, if no reference is provided, the row number of the cell containing the formula. To calculate the right starting point, we use the ROW function. The next argument, rows, is the crux of the problem because this is where we need logic that will figure out the correct starting row for each week. Notice we use an absolute reference because we don't want this reference to change later when we copy the formula down. The starting reference is C5 since this is the first value in the data. In the example shown, we use OFFSET inside of SUM like this: OFFSET($C$5,(ROW()-4)*5,0,5,1) The OFFSET function returns a reference to a range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, and (5) a width in columns. To sum the values returned by OFFSET, we use the SUM function. To build a range that corresponds to the correct 5 rows for each week, we use the OFFSET function.

Notice each week corresponds to 5 rows of data (Monday-Friday) so we will need to sum values in every 5 rows. In this example, the goal is to calculate a weekly total using the data as shown.

Dplyr summarize every five rows