+ Reply to Thread
Results 1 to 4 of 4

Match Cohort date to Payment Date and Amount

  1. #1
    Registered User
    Join Date
    12-04-2014
    Location
    Oakland, CA
    MS-Off Ver
    MAC 2011
    Posts
    3

    Match Cohort date to Payment Date and Amount

    Hi All,

    I'm trying to use SUMIFS INDEX MATCH formula to match payment date and amount to a cohort date, but I have been able to figure it out.

    I have 3 columns with the Cohort Date, Due Date, and Total Due in one sheet and in another sheet the cohort chart, I want to show the total due by Cohort Date at the time the payment was missed.


    The end result is to show how much money is due for each cohort at a future specific date.

    Example Cohort Date 1/1/19 - Total Due on Period 1 (2/2019) X

    This the formula I have tried

    =SUMIFS(Due Date Range,">="&Date,Due Date Range,"<="&EOMONTH(Date,0),INDEX(Total Due Amount,MATCH(Cohort Date,Cohort Date Range,0),0))
    Attached Files Attached Files
    Last edited by pelonsonico; 08-22-2019 at 02:42 PM. Reason: New Excel

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Match Cohort date to Payment Date and Amount

    Hi. Guessing games time...

    can you please amend your sheet to show some manually calculated expected answers?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    12-04-2014
    Location
    Oakland, CA
    MS-Off Ver
    MAC 2011
    Posts
    3

    Re: Match Cohort date to Payment Date and Amount

    Hi Glenn,

    I have updated the excel sheet.

    Thanks,

  4. #4
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Match Cohort date to Payment Date and Amount

    Please Login or Register  to view this content.
    I think this formula will work.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

+ 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. Replies: 16
    Last Post: 01-31-2019, 03:31 PM
  2. How to find Last(Latest) Payment date and Amount from Excel
    By grlinks in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-13-2016, 07:37 AM
  3. [SOLVED] Need to find a negative amount and match it to the positve amount based on Acct# and Date
    By sbrandhorst in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-07-2016, 07:49 PM
  4. formula to subtract payment amount on monthly payment date
    By restingdonkey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2014, 03:39 PM
  5. Replies: 2
    Last Post: 04-28-2013, 02:23 AM
  6. How to match Date and Amount from Sheet1 to Sheet2 with the same Date and Amount?
    By ims0phie in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-23-2013, 06:51 AM
  7. [SOLVED] calculate payment with first payment due date variable?
    By Jody Solbach in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-08-2005, 12:46 PM

Tags for this Thread

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