Sumifs if 2 dates are between another 2 dates.

**kylzb_87** · 03-09-2023, 12:53 AM

I am trying to find the right formula to add the amount in Column F if the dates in columns C fall within the date range in column G (Start of the fortnight) and H (End of the fortnight). I have added a pic of the spreadsheet to assist. THANK YOU SOO MUCH for your help!

**alansidman** · 03-09-2023, 12:56 AM

No picture attached, however,

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**bebo021999** · 03-09-2023, 01:33 AM

Try to follow this format:

=SUMPRODUCT(SUMIFS(Range F,Range G,"<=" & Range C,Range H,">=" & Range C))

adjust each range with actual range

**kylzb_87** · 03-09-2023, 03:15 AM

Payslip CALCULATOR.xlsx Hopefully I have attached the file now. Thank you again for your help!

**bebo021999** · 03-09-2023, 03:29 AM

Which sheet, Where and What is expected outcome?

**kylzb_87** · 03-09-2023, 07:55 AM

I am needing it for both sheets please? The first sheet I am needing the cells in column I to sum the amounts in column F if the date in column C is within the date range in G (Start date) and H (End dates). In the second sheet I am needing the cells in column J to sum the amounts in column F if the dates in column C (Payslip start date) and D (payslip end date) is within the date range in G (Start date) and H (End dates). I hope this makes sense...

**JeteMc** · 03-12-2023, 09:23 PM

On the paydate sheet try pasting the following into cell I4 and then copying down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**kylzb_87** · 03-13-2023, 11:30 PM

Unfortunately it is adding daily amounts for amounts not within the specified dates. Its adding the daily rate for 17/08/2022, 24/08/2022, 31/08/2022 and 7/09/2022 when the date range is 19/8/22- 1/9/22.

**JeteMc** · 03-14-2023, 08:39 AM

Here is a copy of the file with the formula applied in column I.
To check the formula I put the corresponding SUMPRODUCT formula in cell K4. If you run the Evaluate Formula feature (Formulas tab) in cell K4 you'll see that the array shows a zero for the values in all but cells F5:F6 which are the daily amounts for August 24th and 31st.
Let us know if you have any questions.