Build an Accounts Receivable Aging Report in Excel

[TheKa23]

How do you use if() and AND() in the same cell?

[DannyRocksExcels]

@TheKa23

I am having trouble posting a response - not sure why. I will try to send you a message in response.

[TheKa23]

@DannyRocksExcels ok thank you :)

[MrAlexcode]

This is great, I have one question. I added a "status" & "paid" column. When costumer pays I put date in the "paid" column & status column goes to paid or left un-paid till they pay. (used if command to determine paid/un-paid status)

My question is, how to get the "days past due" column to zero once they pay, other wise it continues to count as over due

Days Past due =IF($B$2-E4, $B$2-E4, " ")

Staus Column =IF(F4,"Paid","Un-Paid")

Thank You

Alex Code

[DannyRocksExcels]

@MrAlexcode

Hi Alex -

I have many comments on this video, so I am going to produce an update to it. I am planning to do that this week and I will incorporate your question in the new video.

Sincerely,

Danny Rocks

The Company Rocks

[adebalayor]

on the first spreadsheet, what formula do you have in cell D19 ($80,886)? Is there a way to sum up all the past due automatically without having to select D6:D14?

Thanks

[DannyRocksExcels]

@adebalayor

In cell D19 I use the =SUMIF() Function which has 2 required arguments (Range and Criteria) and one optional argument ([sum_range]). The cells that you reference (D6:D14) are the cells for the "Range" that we need to "look in" to match the "Criteria" of = "Past Due."

So, the answer is "Yes" if you are going to use the "SUMIF Function." You could save yourself some time if you create a "named range" - e.g. "Status" for all of the cells in Column D.

Danny Rocks

[adebalayor]

@adebalayor Thanks Danny! This tutorial is really helpful!

[jhosen20091]

I think theres is a problem with the formula at "0ver 90days" because it also read the amount on current...

[DannyRocksExcels]

@jhosen20091

Good catch! I finally had a chance to go back and review this video and recreate the formulas.

I should have copied over - and editied the formula from cell I6 =IF(AND($E6>=91, $E6>120), $D6, " ")

I appreciate you letting me know this so that I can correct it.

Sincerely,

Danny Rocks

The Company Rocks

[adebalayor]

@jhosen20091 I think so too

[mao3577]

Hi Danny! You are the best. It's super clear the tutorial. Thanks,

[DannyRocksExcels]

@mao3577

Thank you! What a great compliment. I appreciate you writing to tell me this.

Danny Rocks

The Company Rocks

[TheSherridan1]

Thank you...this really helped me out!

[DannyRocksExcels]

@TheSherridan1

My pleasure! Thank you for writing to tell me that I helped you with this video lesson.

Danny Rocks

The Company Rocks

[sanjido]

thanks very helpful for my work

[DannyRocksExcels]

@sanjido

Thank you. I am pleased that I was able to make it easier to use Excel in your work.

Danny Rocks

The Company Rocks

[judzm]

Very informative lessons on excel. You Rock Danny! :)

[DannyRocksExcels]

@judzm

Thank you for writing, I am pleased that you found the video informative.

Danny Rocks

The Company Rocks

[riverhousefoto]

excellent tutorial, thanks.

[DannyRocksExcels]

@riverhousefoto

Thank you - I am pleased that you found this lesson helpful.

Danny Rocks