I need a google sheet formula
A variation of solution mentioned in Finding a sum of cells given address range in google sheets
This question is to about referencing data from some other google sheet file and computing the total.
Click here to make a copy of data sheet
Click here to make a copy of report sheet
I have a data in Employee tables from B column to H column
Data runs from left to right only in B through H
Given a starting address of the cell and given a ending address of the cell
(in the image you see H1 and H2 columns)
I want to find the sum of all cells from the start to end address with respect from B through H
When yellow value dates are changed the sum should be reflected accordingly based on the data of the Empoyee table.
My results are incorrect since it needs to find the sum spanning the cells from B to H column
I have tried with
=let(cols,tocol(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/10q1hgvP8FgdTHkwH4y1ls5Tv1di6XYgyblq4gm_PK-I/edit#gid=103277278","'Employee'!B5:H")
,1),start,((WEEKNUM(D2))-1)*7+(WEEKDAY(D2,2)),end,((WEEKNUM(D3))-1)*7+(WEEKDAY(D3,2)),sum(chooserows(cols,sequence(end-start+1,1,start))))
Please note that Employee sheets are READONLY since they are being fetched from some other external spreadsheets using import formula
>Solution :
You may try:
=let(Σ,importrange("10q1hgvP8FgdTHkwH4y1ls5Tv1di6XYgyblq4gm_PK-I",A6&"!B5:H"),
start_,weeknum(D$2),end_,weeknum(D$3),
sum(filter(tocol(chooserows(Σ,sequence(end_))),isbetween(--tocol(sequence(end_)&sequence(1,7)),--(start_&weekday(D$2,2)),--(end_&weekday(D$3,2))))))
