What functions would I use to get money values over 30, 60, and 90 days old?

Hi,

I am using an excel made Accounts Receivable sheet to keep track of all our invoices. I have put an entry to show how much money is owed to us that is still 30 days old, 60 days old, and 90 days old.

Is there a single function that will alllow me to do this?

Right now I am using adjacent cells in each rom that has a formula of...

if(E5<31,"",F5) , in which E5 is date invoiced and F5 is amount invoice

this formula is used for each following row.

Then I sum all the rows and get the summed amount over 30 days old to satisfy the "Over 30 Days Old," query.

Is there a simplier way of doing this that doesn't require me to input the formula on each new row?

There is an excel sample below to help with my question.

Thanks,
Dallas

Originally Posted by daljaxon

Hi,

I am using an excel made Accounts Receivable sheet to keep track of all our invoices. I have put an entry to show how much money is owed to us that is still 30 days old, 60 days old, and 90 days old.

Is there a single function that will alllow me to do this?

Right now I am using adjacent cells in each rom that has a formula of...

if(E5<31,"",F5) , in which E5 is date invoiced and F5 is amount invoice

this formula is used for each following row.

Then I sum all the rows and get the summed amount over 30 days old to satisfy the "Over 30 Days Old," query.

Is there a simplier way of doing this that doesn't require me to input the formula on each new row?

There is an excel sample below to help with my question.

Thanks,
Dallas

Hi,

with your date in column A and amount in column B, in C1 to put

=IF(A1<TODAY()-30,"",B1)

in D1 put

=IF(AND(A1>TODAY()-60,A1<=TODAY()-30),B1,"")

in E1, F1, G1 put

=IF(AND(A1>TODAY()-90,A1<=TODAY()-60),B1,"")
=IF(AND(A1>TODAY()-120,A1<=TODAY()-90),B1,"")
=IF(A1<=TODAY()-210,B1,"")

and formula fill those down as far as your data goes, then
simply total the columns for the required period figures.

note, formula fill is shown at http://www.mvps.org/dmcritchie/excel/fillhand.htm

hth
---

with your amended attached file,

in D2

=SUMPRODUCT(--(C9:C100<TODAY()-30)*(--(C9:C100>=TODAY()-60))*(D9:D100))

in D4

=SUMPRODUCT(--(C9:C100<TODAY()-60)*(--(C9:C100>=TODAY()-90))*(D9:D100))

in D6

=SUMPRODUCT(--(C9:C100<TODAY()-90)*(--(C9:C100>=TODAY()-120))*(D9:D100))

should go close.
---

HI Bryan,

Thanks for your help. Your first reply looks like another way of doing formulas like what I am using, but as a part of the table. On my amended file, which I apologize for having to redo, I totaled the number of days instead of the dollar amount at first.

On my Accounts Receivable log, the formulas in the colored columns are hidden from view, I have other data entered for invoice information, so entering this data on the table would be a bit too much information.

I used the data you suggested on the second reply, but I could not get this to work correctly. The formulas gave me wrong data. I did not understand the sumproduct function and will study it further to get a better understanding on how it works.

I did entered the formula in the cells specified, but it not return the values that were expected.

Thanks,
Dallas


Edit: Later that night....

I searched and found the function I needed. "Sumif()" function returns the value according the criteria I designate.

In the case of my attached file, in cell D2, I put the formula "=SUMIF(E9:E17,">30",D9:D17)," it returns me the value of all that is still owed after 30 days, which I can use the same for 60 and 90 days. If I extend the range to E500, I can hopefully, and delete rows as accordingly when invoices are paid and added without risking my formula changing.

Thanks alot for you input Bryan, I got to see how the And() function was used.

Dallas

Originally Posted by daljaxon

HI Bryan,

Thanks for your help. Your first reply looks like another way of doing formulas like what I am using, but as a part of the table. On my amended file, which I apologize for having to redo, I totaled the number of days instead of the dollar amount at first.

On my Accounts Receivable log, the formulas in the colored columns are hidden from view, I have other data entered for invoice information, so entering this data on the table would be a bit too much information.

I used the data you suggested on the second reply, but I could not get this to work correctly. The formulas gave me wrong data. I did not understand the sumproduct function and will study it further to get a better understanding on how it works.

I did entered the formula in the cells specified, but it not return the values that were expected.

Thanks,
Dallas


Edit: Later that night....

I searched and found the function I needed. "Sumif()" function returns the value according the criteria I designate.

In the case of my attached file, in cell D2, I put the formula "=SUMIF(E9:E17,">30",D9:D17)," it returns me the value of all that is still owed after 30 days, which I can use the same for 60 and 90 days. If I extend the range to E500, I can hopefully, and delete rows as accordingly when invoices are paid and added without risking my formula changing.

Thanks alot for you input Bryan, I got to see how the And() function was used.

Dallas

Hi,

yes, the formula were for the more usual figures where the 30 day figure excludes the 60 day, and the 60 day excludes the 90 day, and the 90 day excludes the 120 day etc. The Sumproduct version for your newly amended figures would be

=SUMPRODUCT(--(C9:C100<TODAY()-30)*(D9:D100))
=SUMPRODUCT(--(C9:C100<TODAY()-60)*(D9:D100))
=SUMPRODUCT(--(C9:C100<TODAY()-90)*(D9:D100))

which would agree with the figures shown, but this can also be achieved via the Sumif with a helper column as you have discoverd.

Good to see that you have a solution, and thanks for your response.
---

Originally Posted by Bryan Hessey

Hi,

yes, the formula were for the more usual figures where the 30 day figure excludes the 60 day, and the 60 day excludes the 90 day, and the 90 day excludes the 120 day etc. The Sumproduct version for your newly amended figures would be

=SUMPRODUCT(--(C9:C100<TODAY()-30)*(D9:D100))
=SUMPRODUCT(--(C9:C100<TODAY()-60)*(D9:D100))
=SUMPRODUCT(--(C9:C100<TODAY()-90)*(D9:D100))

which would agree with the figures shown, but this can also be achieved via the Sumif with a helper column as you have discoverd.

HI Bryan,

Thanks again for your information, I was looking for a way of using a formula without a helper column as you specified. This will help quite a bit in on coming projects.

I overlooked the possibility of boxing in a display amounts criticed in specific days, your second set of formula's will work well for this, ic.. 31-60 days, 61-90 days, etc...

My problem now is that, I only understand the basics in using Excel, and don't understand or see how the calculation steps work in the formula above, which does work well for this application.
Is there a place on line I can school/study on my own to get a better understanding on this works.

For instance, what does the "--" stand for in SUMPRODUCT(--(C9:C100<TODAY()-30)*(D9:D100))?
I see that "(C9:C100<TODAY()-30)" looks for every thing that is over 30 days, but how does "*(D9:D100)" give me the end result.
Obviously I have a bit to learn, so do you have a suggestion on where to start?

Thanks,
Dallas

Originally Posted by daljaxon

HI Bryan,

Thanks again for your information, I was looking for a way of using a formula without a helper column as you specified. This will help quite a bit in on coming projects.

I overlooked the possibility of boxing in a display amounts criticed in specific days, your second set of formula's will work well for this, ic.. 31-60 days, 61-90 days, etc...

My problem now is that, I only understand the basics in using Excel, and don't understand or see how the calculation steps work in the formula above, which does work well for this application.
Is there a place on line I can school/study on my own to get a better understanding on this works.

For instance, what does the "--" stand for in SUMPRODUCT(--(C9:C100<TODAY()-30)*(D9:D100))?
I see that "(C9:C100<TODAY()-30)" looks for every thing that is over 30 days, but how does "*(D9:D100)" give me the end result.
Obviously I have a bit to learn, so do you have a suggestion on where to start?

Thanks,
Dallas

Hi,

for the sumProduct I think Bob Phillips site at http://www.xldynamic.com/source/xld.SUMPRODUCT.html gives a full explanation. To answer your question, the double unary is to convert the True/False reples to 1/0 in order to use them mathematically, the first range was a selection and the last range was the range to be accumulated, this sumproduct being much the same in design as a SumIf, thus the sum totals 0 or 1 * each cell of D9 to D100

hth
---