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.
HRS Calculation EXAMPLE.png
Cell Formulas
Range Formula
B14:B39 B14 =LET(p,MATCH(B6,{"Weekly","Fortnightly","Monthly"},0),SEQUENCE(CHOOSE(p,26,13,6),,B6,CHOOSE(p,7,14,30.41)))
C14:C39 C14 =B14#+IF(B6="Weekly",6,IF(B6="Monthly",29.41,13))
H14:H26 H14 =SUMIFS(Payslip_Hours,B14#,">="&I14#,C14#,"<="&K14#)
K14:K26 k14 =LET(p,MATCH(B7,{"Fortnightly","Monthly","Quarterly","Milestone","Lump Sum"},0),SEQUENCE(CHOOSE(p,13,6,2,2,1),,B8,CHOOSE(p,14,30.41,91,91,182)))
M14:M26 M14 ==K14#+IFS(B7="Fortnightly",13,B7="Monthly",29.41,B7="Quarterly",90,B7="Quarterly",90,B7="Milestone",90,B7="Lump Sum",181)
Dynamic array formulas.
Named Ranges
Name Refers To Cells
Payslip_Hours =Sheet1!$D$14:$D$39
Bookmarks