Excel Sum Index Match Date Criteria
Daily Data Table (Extract) (Current Size: Rows 500 Columns 1500) Emp 27/10/19 28/10/19 29/10/19 30/10/19 123456 5.0 7.0 7.0 7.0 234567 6.0 8.0 8.0 8.0 Weekly...
Daily Data table (extract) (current size: rows 500 columns 1500)
| Emp | 27/10/19 | 28/10/19 | 29/10/19 | 30/10/19 |
|---|---|---|---|---|
| 123456 | 5.0 | 7.0 | 7.0 | 7.0 |
| 234567 | 6.0 | 8.0 | 8.0 | 8.0 |
Weekly Summary table (extract)
| Emp | 27/10-02/11/19 | 03/11-09/11/19 |
|---|---|---|
| 123456 | ??? | ??? |
| 234567 | ??? | ??? |
These are structured table on different worksheet within the same workbook. Any formula to use in ??? to calculate sum of 7 days as described in the header cell?
Thank you.
1 Answer
Try the formula solution
In "Weekly Summary Table" B8, formula copied right to C8 and all copied down :
1] Using SUMIFS function
=SUMIFS(INDEX($2:$3,MATCH($A8,$A$2:$A$3,0),0),$1:$1,">="&REPLACE(B$7,6,6,""),$1:$1,"<="&RIGHT(B$7,8))
Or,
2] Using SUM+OFFSET function
=SUM(OFFSET($A$1,MATCH($A8,$A$2:$A$3,0),MATCH(0+REPLACE(B$7,6,6,""),$1:$1,0)-1,1,7))
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
