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

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
---