SUMIF in airtable with variable date fields

[sftechchick]

I have two tables, one with a list of payroll dates(vertically) for a company and another that is a payroll download that includes payroll dates(vertically), gross pay and employee information. 

In excel I used the sumif formula to say in sheet 2 sum gross pay for all the rows that equal the payroll start and end date in sheet 1.

Example: There are 10 employees so there are 10 rows with different employees names and gross pay amounts in sheet 2. In sheet 1 I have a single row with the start date 1/1/2023 and end date 1/15/2023. I want to be able to to say sum the gross pay for all the rows in sheet 2 that have a start date of 1/1/2023 and end date of 1/15/2023. I need these dates to be referencing something though so that it will sum different date ranges based on the row it is in. So if the next row down in sheet 1 is 1/16/2023 and 1/31/2023 then it will sum all the gross pay amounts in sheet 2 for that date range. 

I tried the rollup but it is not dynamic enough, and neither is the automations. Is there another way to do this in airtable? 

[TheTimeSavingCo]

I think the simplest solution would be to use a script for this I'm afraid

A non-scripting alternative is possible but tedious to set up and involves you linking all of the records in Sheet 2 to that single record in Sheet 1 with the start and end date.  You'd then have lookup fields in Sheet 2 to display the start and end date from Sheet 1, and a formula fielda to check if that record's dates was within the lookup start and end dates

You'd then link all of the records which the formula field identified as true to the record in Sheet 1 in another linked field and so if you did a rollup on those links it would give you the sum of all of the records within that date range

Finally, you'd need to clear the original link field in Sheet 1 so that your automation could run for the next record