Forum Discussion

ChrisJones90's avatar
ChrisJones90
Copper Contributor
May 28, 2024

Excell formula

It has been awhile since i worked on advanced formulas in excel.

I am working on a budget sheet and am having trouble on a formula to sum numbers in column F based on a range of numbers (a week of rows) in column E that can be extended to other rows (drag fill) to get a dollar amount needed for a specific day. This is what i have as of now;

=IF E3:E40,>=H46-1,and<=H46+5,SUM(F3,F40)

E is the due date column in a table

H is the reference date outside the table with the range (minus a day and plus 5 days)

F is the column of numbers in the table i want summed.

 

So if i put in 15 in cell H, i would like it to sum the numbers in column F that are in the range of 14-20 in column E.

Is it possible to get it to wrap around back to 1 since there are no more than 31 days in a month, rather then the range increasing to 35?

 

 

excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 29, 2024

    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.

Resources