Sum range totals in a date range where range does not match criteria

[Pepperoni]

Hi everyone,

I have been searching for a way to calculate a range of cells that meet two date criteria, however the problem I have is figuring out how to use SUMPRODUCT or similar as the sum range is dynamic.
My goal is that users will be able to enter a range of figures into an 'Hours' Column, that may or may not be the same cell range as the date columns - I have attached. Users need to select two criteria for the sheet to auto calculate the date range required in B2 and B3.
The dates in I and K are the date range that the Instalment Start and End Dates in B and C need to match with.

So far, I have a great formula for working out a 'Weekly' range, however it all fails if any other instalment period is selected. I have the 'Hours Calculated' column set as a Named Range, however I know that this is causing the issues if the instalment selector is changed (currently using SUMIFS...). All research so far says I need to combine SUMPRODUCT and SUMIF - but I jut cannot get anything to work!

Still quite new to advanced formulas and struggling how to nest stuff properly. Any help is appreciated! Thank you.

Test - Hours Calculation - Sum.xlsx
	A	B	C	D	E	F	G	H	I		K
2	Instalments	Weekly
3	Repayment Frequency	Fortnightly
4	Start Date	1/01/2022
5	End Date	1/07/2022
6	Instalment Start	1/01/2022
7
8		Instalment Start	Instalment End	Hours Calculated		Cumulative Hours			Repayment Start		Repayment End
9	0	1/01/2022	7/01/2022	20		40			1/01/2022		14/01/2022
10	1	8/01/2022	14/01/2022	20		0			15/01/2022		28/01/2022
11	2	15/01/2022	21/01/2022	20		0			29/01/2022		11/02/2022
12	3	22/01/2022	28/01/2022	20		0			12/02/2022		25/02/2022
13	4	29/01/2022	4/02/2022	20		0			26/02/2022		11/03/2022
14	5	5/02/2022	11/02/2022	20		0			12/03/2022		25/03/2022
15	6	12/02/2022	18/02/2022			0			26/03/2022		8/04/2022
16	7	19/02/2022	25/02/2022			0			9/04/2022		22/04/2022
17	8	26/02/2022	4/03/2022			0			23/04/2022		6/05/2022
18	9	5/03/2022	11/03/2022			0			7/05/2022		20/05/2022
19	10	12/03/2022	18/03/2022			0			21/05/2022		3/06/2022
20	11	19/03/2022	25/03/2022			0			4/06/2022		17/06/2022
21	12	26/03/2022	1/04/2022			0			18/06/2022		1/07/2022
22		2/04/2022	8/04/2022
23		9/04/2022	15/04/2022
24		16/04/2022	22/04/2022
25		23/04/2022	29/04/2022
26		30/04/2022	6/05/2022
27		7/05/2022	13/05/2022
28		14/05/2022	20/05/2022
29		21/05/2022	27/05/2022
30		28/05/2022	3/06/2022
31		4/06/2022	10/06/2022
32		11/06/2022	17/06/2022
33		18/06/2022	24/06/2022
34		25/06/2022	1/07/2022
Sheet1

Cell Formulas
Range	Formula
B9:B34	B9	=LET(p,MATCH(B2,{"Weekly","Fortnightly","Monthly"},0),SEQUENCE(CHOOSE(p,26,13,6),,B6,CHOOSE(p,7,14,30.41)))
C9:C34	C9	=B9#+IF(B2="Weekly",6,IF(B2="Monthly",29.41,13))
F9:F21	F9	=SUMIFS(Payslip_Hours,B9#,">="&I9#,C9#,"<="&K9#)
I9:I21	I9	=LET(p,MATCH(B3,{"Fortnightly","Monthly","Quarterly","Milestone","Lump Sum"},0),SEQUENCE(CHOOSE(p,13,6,2,2,1),,B4,CHOOSE(p,14,30.41,91,91,182)))
K9:K21	K9	=I9#+IFS(B3="Fortnightly",13,B3="Monthly",29.41,B3="Quarterly",90,B3="Quarterly",90,B3="Milestone",90,B3="Lump Sum",181)
A9:A21	A9	=IFERROR(INT((B9+4-DATE(YEAR(B9+365),-11,-1))/7),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Payslip_Hours	=Sheet1!$D$13:$D$38	F9

 

[jasonb75]

The only problem that I see is that the named range doesn't include the first 4 rows of data.

 

[Pepperoni]

The only problem that I see is that the named range doesn't include the first 4 rows of data.

Hi Jacob - sorry, just realised that I entered it across funny. I've fixed it to include the entire range: D9:D34, however still have the original issue. CAlculates well for weekly (rows all match) however, if it's changed to fortnightly, it won't calculate.