Sum up a range of cells - based on matching fortnight beginning date

[rstuart]

I have a budget table.

Fortnightly dates are listed in Column A - where I summarise total expenses per fortnight from table on right.

I wish to create formula i can autofill that will SUM UP (from table on right) the amounts across 7 columns and 10 rows (2 weeks) based on the fortnight beginning date.

Below: I autosum the fortnight beginning 1 April - 14 April (the table continues like this for 2 years)

W/b date	Total Expenses		Daily expense	Exp 1	Exp 2	Exp 3	Exp 4	Exp 5	Exp 6	Exp 7
Friday, 1 April 2022	$1,507.07		Friday, 1 April 2022	$50.00	$300.00		$378.09
Friday, 15 April 2022			Monday, 4 April 2022			$355.00
Friday, 29 April 2022			Tuesday, 5 April 2022
Friday, 13 May 2022			Wednesday, 6 April 2022
Friday, 27 May 2022			Thursday, 7 April 2022
Friday, 10 June 2022			Friday, 8 April 2022					$34.00	$17.99
Friday, 24 June 2022			Monday, 11 April 2022			$355.00
Friday, 8 July 2022			Tuesday, 12 April 2022
Friday, 22 July 2022			Wednesday, 13 April 2022
Friday, 5 August 2022			Thursday, 14 April 2022							$16.99
Friday, 19 August 2022
Friday, 2 September 2022

 

[jtakw]

Hi,

If I understand correctly:

Change/Adjust cell range/references to include your Entire Table (for the 2 years).

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	W/b date	Total Expenses		Daily expense	Exp 1	Exp 2	Exp 3	Exp 4	Exp 5	Exp 6	Exp 7
2	4/1/2022	$1,507.07		4/1/2022	$50.00	$300.00		$378.09
3	4/15/2022	$265.00		4/4/2022			$355.00
4	4/29/2022	$0.00		4/5/2022
5	5/13/2022	$0.00		4/6/2022
6	5/27/2022	$0.00		4/7/2022
7	6/10/2022	$0.00		4/8/2022					$34.00	$17.99
8	6/24/2022	$0.00		4/11/2022			$355.00
9	7/8/2022	$0.00		4/12/2022
10	7/22/2022	$0.00		4/13/2022
11	8/5/2022	$0.00		4/14/2022							$16.99
12	8/19/2022	$0.00		4/15/2022		55
13	9/2/2022	$0.00		4/16/2022					99		111
Sheet1063

Cell Formulas
Range	Formula
B2:B13	B2	=SUMPRODUCT((D$2:D$13>=A2)*(D$2:D$13<A2+14)*E$2:K$13)

 

[rstuart]

Perfect, thanks!

 

[jtakw]

You're welcome, thanks for the feedback.