Excell formula

ChrisJones90 

To create a formula that sums values in column F based on a range of dates in column E, and to handle the wrap-around for days of the month, you can use the SUMIFS function along with some logic to handle the date wrapping.

Here's how you can achieve this:

1. Define the Range and Criteria:
• E is your due date column.
• F is your amount column.
• H46 is your reference date.
2. Handle the Date Wrapping:
• Use a helper column to handle the wrap-around logic for dates.
• Use a combination of MOD and date functions to determine the days correctly.

Step-by-Step Solution:

Step 1: Create a Helper Column for Date Wrapping

Let's assume column G is your helper column where you calculate the wrap-around dates.

In cell G3, enter the following formula and drag it down:

=MOD(E3 - DAY(E3) + (H46-1), 31) + 1

This formula adjusts the date in E3 to handle wrapping around the end of the month.

Step 2: Use the SUMIFS Function

In cell I3 (or wherever you want the result), use the following SUMIFS formula:

=SUMIFS(F3:F40, G3:G40, ">=" & MOD(DAY(H46)-1, 31) + 1, G3:G40, "<=" & MOD(DAY(H46)+5, 31))

Here's how this works:

• F3:F40 is the range of values you want to sum.
• G3:G40 is the helper column with the wrapped dates.
• ">=" & MOD(DAY(H46)-1, 31) + 1 ensures that you are checking dates starting from one day before the reference date.
• "<=" & MOD(DAY(H46)+5, 31) ensures that you are checking dates up to five days after the reference date, wrapped around the end of the month.

Example:

• If H46 is 15, G3:G40 will correctly handle the dates around the end of the month and check for values between 14 and 20.
• The SUMIFS function then sums up the corresponding values in column F that fall within this range.

Summary:

This approach leverages a helper column to manage the date wrapping logic and uses SUMIFS to perform the conditional sum based on the adjusted dates. This way, you can extend the range to handle wrapping around the end of the month, ensuring that your calculations are accurate.

The text was created with the help of AI.

My answers are voluntary and without guarantee!

I hope this solution works for your budget sheet!

Was the answer useful? Mark as best response and like it!

This will help all forum participants.