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.

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.

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.

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.

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.