Using SUMIF Greater Than/Less Than in a Date Range

I've been working on an Excel budget worksheet to help me understand what bills are needing to be paid on each paycheck. I get paid every two weeks. In column I lists all the pay dates for the year. This pay list is created by the formula =SEQUENCE(26,1,I2,14).

Bills Worksheet

Columns A-F itemize each bill. Column A is the bill frequency (weekly, bi-weekly, monthly and yearly), C is the bill amount, D is a calculation based on approximate monthly total, E is the Day its due, and F is whether the bill is paid on a credit card. If on a credit card, I have another sheet that lists credit card monthly payments and is added to the minimum due.

The problem I'm running into is calculating column J for the monthly bills. Looking at Row 2 for example, Rent, the bill is due the 1st of each month. I would like to go through each pay period, and add the Rent amount (cell C2), to all pay periods that this would be included in. For example, 1/26/2024 (cell I4) - 2/8/2024 (cell I5 -1). This would be applied for all bills in all pay periods when applicable.

My summary of the calculation is as below, with the finished code I have so far. This works with when the end day of the pay period is is numerically higher than the start date, i.e. 1/12/2024 - 1/26/2026. But it does not work for period ending in a new month, i.e. 1/26/2024 - 2/9/2024.

IF A:A = W (Weekly)
    SUM(SUMIFS(C:C, A:A, "=W", F:F, "<>X") * 2)
SUM C:C

- Plus -

IF A:A = B (Bi-weekly)
    SUMIFS(C:C, A:A, "=B", F:F, "<>X")
SUM C:C

- Plus -

IF A:A = M (Monthly)
    IF E:E as <day of month> >= I3
    and E:E as <day of month> < I4
    and F:F not X
SUM C:C 

- Plus -

IF A:A = Y (Yearly)
    SUMIFS(C:C, A:A, "=Y", E:E,">="&I3, E:E, "<"&I4, F:F, "<>X")
SUM C:C

Finished Code (so far) for column J:

=SUMIFS(C:C, A:A, "=W", F:F, "<>X")*2 + SUMIFS(C:C, A:A, "=B", F:F, "<>X") + SUMIFS(C:C, A:A, "=M", E:E,">="&DAY(I3), E:E, "<"&DAY(I4), F:F, "<>X") + SUMIFS(C:C, A:A, "=Y", E:E,">="&I3, E:E, "<"&I4, F:F, "<>X")

Does anyone know a good way to perform this with the end date in a new month? I like to accomplish this in a formula, and not within VBA code or macros.

Tried using different Date formulas for the Monthly frequency, including

SUMIFS(D4, DATE(YEAR(I3), MONTH(I3), E:E), ">="&DATE(YEAR(I3), MONTH(I3), DAY(I3)), DATE(YEAR(I4), MONTH(I4), E:E), ">="&DATE(YEAR(I4), MONTH(I4), DAY(I4)))

Solution

I suggest you use two separate sumifs in the cases where the two-week period is split across two different months like this:

=IF(MONTH(I4)=MONTH(I3),
  SUMIFS(C:C,A:A,"M",E:E,">="&DAY(I3),E:E,"<"&DAY(I4),F:F,"<>"&"X"),
  SUMIFS(C:C,A:A,"M",E:E,">="&DAY(I3),F:F,"<>"&"X")+SUMIFS(C:C,A:A,"M",E:E,"<"&DAY(I4),F:F,"<>"&"X"))

This only does the month part - I think the rest should be OK.

Some test data: