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

#### Pepperoni

##### New Member
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
ABCDEFGHIK
2InstalmentsWeekly
3Repayment FrequencyFortnightly
4Start Date1/01/2022
5End Date1/07/2022
6Instalment Start1/01/2022
7
8Instalment StartInstalment EndHours CalculatedCumulative HoursRepayment StartRepayment End
901/01/20227/01/202220401/01/202214/01/2022
1018/01/202214/01/202220015/01/202228/01/2022
11215/01/202221/01/202220029/01/202211/02/2022
12322/01/202228/01/202220012/02/202225/02/2022
13429/01/20224/02/202220026/02/202211/03/2022
1455/02/202211/02/202220012/03/202225/03/2022
15612/02/202218/02/2022026/03/20228/04/2022
16719/02/202225/02/202209/04/202222/04/2022
17826/02/20224/03/2022023/04/20226/05/2022
1895/03/202211/03/202207/05/202220/05/2022
191012/03/202218/03/2022021/05/20223/06/2022
201119/03/202225/03/202204/06/202217/06/2022
211226/03/20221/04/2022018/06/20221/07/2022
222/04/20228/04/2022
239/04/202215/04/2022
2416/04/202222/04/2022
2523/04/202229/04/2022
2630/04/20226/05/2022
277/05/202213/05/2022
2814/05/202220/05/2022
2921/05/202227/05/2022
3028/05/20223/06/2022
314/06/202210/06/2022
3211/06/202217/06/2022
3318/06/202224/06/2022
3425/06/20221/07/2022
Sheet1
Cell Formulas
RangeFormula
B9:B34B9=LET(p,MATCH(B2,{"Weekly","Fortnightly","Monthly"},0),SEQUENCE(CHOOSE(p,26,13,6),,B6,CHOOSE(p,7,14,30.41)))
C9:C34C9=B9#+IF(B2="Weekly",6,IF(B2="Monthly",29.41,13))
F9:F21F9=SUMIFS(Payslip_Hours,B9#,">="&I9#,C9#,"<="&K9#)
I9:I21I9=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:K21K9=I9#+IFS(B3="Fortnightly",13,B3="Monthly",29.41,B3="Quarterly",90,B3="Quarterly",90,B3="Milestone",90,B3="Lump Sum",181)
A9:A21A9=IFERROR(INT((B9+4-DATE(YEAR(B9+365),-11,-1))/7),"")
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Payslip_Hours=Sheet1!\$D\$13:\$D\$38F9

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
The only problem that I see is that the named range doesn't include the first 4 rows of data.

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.

Replies
4
Views
69
Replies
12
Views
117
Replies
11
Views
400
Replies
2
Views
372
Replies
3
Views
240

1,203,744
Messages
6,057,116
Members
444,905
Latest member
Iamtryingman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back