+ Reply to Thread
Results 1 to 3 of 3

Summing/Ageing of transactions into buckets

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,685

    Summing/Ageing of transactions into buckets

    Hi,

    I have a report and I would like to age the transactions based on todays date and the posting date (column B on the sheet “Transactions” into the following categories:
    0-30
    30-60
    60-90
    90+

    What is the formula to do this but that would also match the GL in column C on the “Bsheet tab” with column D)
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Summing/Ageing of transactions into buckets

    in E5
    =SUMPRODUCT((Transactions!$D$5:$D$11=$C5)*(TODAY()-Transactions!$B$5:$B$11>=0)*(TODAY()-Transactions!$B$5:$B$11<=30)*(Transactions!$H$5:$H$11))
    in F5
    =SUMPRODUCT((Transactions!$D$5:$D$11=$C5)*(TODAY()-Transactions!$B$5:$B$11>=31)*(TODAY()-Transactions!$B$5:$B$11<=60)*(Transactions!$H$5:$H$11))
    in G5
    =SUMPRODUCT((Transactions!$D$5:$D$11=$C5)*(TODAY()-Transactions!$B$5:$B$11>=61)*(TODAY()-Transactions!$B$5:$B$11<=90)*(Transactions!$H$5:$H$11))
    in H5
    =SUMPRODUCT((Transactions!$D$5:$D$11=$C5)*(TODAY()-Transactions!$B$5:$B$11>=91)*(Transactions!$H$5:$H$11))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,685

    Re: Summing/Ageing of transactions into buckets

    Thanks Special-K! This worked brilliantly

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Difficult: Counting unique transactions in each month, then summing "lifetime value"
    By semantics in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-08-2017, 04:57 AM
  2. Replies: 3
    Last Post: 07-04-2014, 09:57 PM
  3. Replies: 22
    Last Post: 09-20-2013, 02:00 PM
  4. Replies: 2
    Last Post: 09-17-2013, 07:12 PM
  5. sumproduct...into buckets
    By jw01 in forum Excel General
    Replies: 10
    Last Post: 03-09-2011, 11:57 AM
  6. Summing all transactions that are on a Friday
    By skatmandu2002 in forum Excel General
    Replies: 2
    Last Post: 12-07-2009, 12:26 PM
  7. Buckets with if formula
    By mangesh in forum Excel General
    Replies: 2
    Last Post: 02-01-2008, 03:41 AM

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