SUM with OFFSET | Fix #REF if Starting Date isn't 1st of Month

  • Hello -

    In attached workbook, I needed the Monthly Interest and Monthly Serv Fee columns to population with the monthly sum, if the date was the last day of the month; I currently am using SUM with OFFSET, and refencing the number of days in a given month in the OFFSET formula.

    This works nicely, except at the very start of the date range, if the beginning date is not the 1st business day of the month. This is because the OFFSET formulas is then trying to sum above Row 1, which obviously returns #REF!.

    Does anyone have any ideas on how to fix this problem, or rework my approach using SUM with OFFSET here?

    OFFSET formula within SUM formula: OFFSET(B2,(-DAY(EOMONTH(A2,0))+1),0)

  • Additional Notes:

    1. Date in Cell A2 changes all other dates below.

    2. I'm also using an IF statement in tangent of SUM/OFFSET, to return blank if date is not EOM.

  • This thread can be considered closed. I changed tactics and went with ROW() referencing for the SUM/OFFSET ranges. Thanks all.

  • OFFSET can be used with any function expecting a reference argument. For example, the formula SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a 3-row by 1-column range that is 1 row below and 2 columns to the right of cell C2.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!