I have two Google Sheets. One is a list of dates in sequence in Column A (9/1/24; 9/2/24; etc..) and in Column B are number values associated with some of the dates.
Sheet 1:
9/1/2024
9/2/2024
9/3/2024
9/4/2024
9/5/2024
9/6/2024
9/7/2024
9/8/2024
9/9/2024 $750
9/10/2024
9/11/2024 $500
9/12/2024
9/13/2024
9/14/2024
9/15/2024 $400
The other sheet is a list of dates of Sundays in Column A (9/1/24; 9/8/24; 9/15/24; etc…) and in Column B, I would like to write a formula that would search the first sheet for the date in the second sheet, then return the sum of the number values that are in its Column B associated with that week. I’m having trouble figuring out how to have the formula sum the 7 values beside the date if found.
Sheet 2:
Income
9/01/24
9/08/24
9/15/24
9/22/24
9/29/24
10/06/24
10/13/24
10/20/24
I am making a budgeting tool.
I have tried searching Google for the answers but to no avail. I’m familiar with VLookup and XLookup and SumIF, so I’m sure there’s a simple way that you guys will be able to point out.
Thanks!
Utilize WEEKNUM() function. Filter data based on week number then sum up. Try-
=MAP(D1:D8,LAMBDA(x,IFERROR(SUM(FILTER(Sheet1!B1:B,INDEX(WEEKNUM(Sheet1!A1:A))=WEEKNUM(x))),0)))
I have used MAP() function to make the formula dynamic spill so that you do not need to drag down the formula manually to each cell.
2