Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I Lookup and sum payments for cash flow forecast?
My spreedsheet produces a daily cash position forecast based on fixed
expenses, payroll, and the expected date of incoming payments. I am using the following to lookup incoming payments from a table of our projects based on the date they are expected to be recieved. =IF(ISERROR(OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)),0,OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)) And it works! :) As long as none of the payments are expected on the same date :( How could I get it to sum amounts when/if more than one payment is due on the same date? Thank you. |
#2
|
|||
|
|||
How can I Lookup and sum payments for cash flow forecast?
I *think* this whole equation, with allowance for multiple entries, boils
down to =SUMIF(J:J,A46,L:L) (my reading is that the dates are in column J and the amounts in column L; adjust the formula above if that's incorrect). The A46 indicates the date for which this equation is pulling in expected payments. --Bruce "Aaron" wrote: My spreedsheet produces a daily cash position forecast based on fixed expenses, payroll, and the expected date of incoming payments. I am using the following to lookup incoming payments from a table of our projects based on the date they are expected to be recieved. =IF(ISERROR(OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)),0,OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)) And it works! :) As long as none of the payments are expected on the same date :( How could I get it to sum amounts when/if more than one payment is due on the same date? Thank you. |
#3
|
|||
|
|||
How can I Lookup and sum payments for cash flow forecast?
Thank youy for your response.
Ok, I understand the sumif() function and I can get that to work on its own. I am having a liitle trouble visualizing how to integrate that into my existing equation. Or should I create a separate column to perform the sumif function separately? "bpeltzer" wrote: I *think* this whole equation, with allowance for multiple entries, boils down to =SUMIF(J:J,A46,L:L) (my reading is that the dates are in column J and the amounts in column L; adjust the formula above if that's incorrect). The A46 indicates the date for which this equation is pulling in expected payments. --Bruce "Aaron" wrote: My spreedsheet produces a daily cash position forecast based on fixed expenses, payroll, and the expected date of incoming payments. I am using the following to lookup incoming payments from a table of our projects based on the date they are expected to be recieved. =IF(ISERROR(OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)),0,OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)) And it works! :) As long as none of the payments are expected on the same date :( How could I get it to sum amounts when/if more than one payment is due on the same date? Thank you. |
#4
|
|||
|
|||
How can I Lookup and sum payments for cash flow forecast?
That SUMIF can replace your entire equation!
"Aaron" wrote: Thank youy for your response. Ok, I understand the sumif() function and I can get that to work on its own. I am having a liitle trouble visualizing how to integrate that into my existing equation. Or should I create a separate column to perform the sumif function separately? "bpeltzer" wrote: I *think* this whole equation, with allowance for multiple entries, boils down to =SUMIF(J:J,A46,L:L) (my reading is that the dates are in column J and the amounts in column L; adjust the formula above if that's incorrect). The A46 indicates the date for which this equation is pulling in expected payments. --Bruce "Aaron" wrote: My spreedsheet produces a daily cash position forecast based on fixed expenses, payroll, and the expected date of incoming payments. I am using the following to lookup incoming payments from a table of our projects based on the date they are expected to be recieved. =IF(ISERROR(OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)),0,OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)) And it works! :) As long as none of the payments are expected on the same date :( How could I get it to sum amounts when/if more than one payment is due on the same date? Thank you. |
#5
|
|||
|
|||
How can I Lookup and sum payments for cash flow forecast?
I think I just figured it out. I can use it instead of that other equation.
Thank you. :) "bpeltzer" wrote: I *think* this whole equation, with allowance for multiple entries, boils down to =SUMIF(J:J,A46,L:L) (my reading is that the dates are in column J and the amounts in column L; adjust the formula above if that's incorrect). The A46 indicates the date for which this equation is pulling in expected payments. --Bruce "Aaron" wrote: My spreedsheet produces a daily cash position forecast based on fixed expenses, payroll, and the expected date of incoming payments. I am using the following to lookup incoming payments from a table of our projects based on the date they are expected to be recieved. =IF(ISERROR(OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)),0,OFFSET('Project Information'!$J$3,MATCH(A46,'Project Information'!$J$3:$J$3309,0)-1,2,1,1)) And it works! :) As long as none of the payments are expected on the same date :( How could I get it to sum amounts when/if more than one payment is due on the same date? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|