+ Reply to Thread
Results 1 to 2 of 2

Formula to determine total overdue values by length of overdue

  1. #1
    Registered User
    Join Date
    04-16-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    2

    Formula to determine total overdue values by length of overdue

    I am trying to put together a formula to determine the total values of overdue invoices by the length of their overdue (60+, 90+ and 120+), however I cannot seem to get it quite correct.

    I have multiple sheets that this works from and the data n on individual accounts (by monthly invoice) and then summarised on an additional sheet.

    The formula that I am using is: =SUMIF('120518667!$C$2:$C$150,"=>"&$E$1-60,'120518667'!$H$2:$H$150) for 60+ days overdue, and with variants of "=>"&$E$1-90, for 90+ days and "=>"&$E$1-120 for 120+ days.
    '120518667!$C$2:$C$150, refers to the date; and
    '120518667'!$H$2:$H$150 refers to the invoice amount.

    However my problem is that my retrieved SUM totals are getting higher rather than lower (i.e the value returned for invoices over 120+ days old is higher than that for invoices over 60+ days).

    Where have I gone wrong in my formula?

  2. #2
    Registered User
    Join Date
    04-16-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Formula to determine total overdue values by length of overdue

    Ok, so I kinda found a way to do it via the very long way around by trial and error. But I am sure there has got to be a far simpler way.

    So, Step 1:
    In cell E7
    =SUMIF('120518667'!$C$2:$C$150,">="&E$101,'120518667'!$H$2:$H$150) where Cell E101 = TODAY(-90)
    Step 2:
    In cell E8
    =SUM(SUMIF('120518667'!$C$2:$C$150,">="&F$101,'120518667'!$H$2:$H$150)-E$7)

    This gives me the value of everything overdue beyond 90 days of today.

    If anyone can come up with a formula that works and is simpler than the above please let me know!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1