Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2010
    Location
    Davenport, Iowa
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    =IF(OR(MONTH(B370)=12,I370=0),SUM(G359:G370),"")

    In an amortization table, the above formula is used to sum a range of 12 principal payments (359:370) when the date in the B column (370) = December. Works fine!

    =IF(OR(MONTH(B370)=12,I370=0),SUM(G359:G370),"")

    Here's the problem. The formula tests the Ending Balance column (I) for a zero balance, and if so, the formula should sum the principal payments made during that current calendar year. For example, if the ending balance becomes zero in the 8th month (column B), then the formula should sum the principal payments (column G) in the subsequent 8 rows, not 12.

    =IF(OR(MONTH(B370)=12,I370=0),SUM((G359:G370),"")

    I know the problem is with the SUM portion of the formula. The range of the SUM needs to be defined by the MONTH result. I just don't know how to do that.

    Any and all guidance would be appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Something like this perhaps?
    =IF(OR(MONTH(B370)=12,I370=0),SUM(OFFSET(G370,1-MONTH(B370),0,MONTH(B370),1)),"")

    Steve

  3. #3
    New Lounger
    Join Date
    Jul 2010
    Location
    Davenport, Iowa
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    YES!!!

    Thanks Steve. Thank you very much. Exactly what was needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •