Use COUNTIF and SUMIF to Identify and Total Past Due Invoice Amounts in Excel

Another viewer asks for my help in identifying invoices that are past due. She wants to use the =NOW() function as a reference point in the formula, so that the list is always current.

I also use the =COUNTIF() and =SUMIF() Functions in this lesson.

Finally, I show you how to use a great new Filtering Feature in Excel 2007 - Filter by "Next Week!"

Learn about my new DVD, "The 50 Best Tips, Tricks & Techniques for Excel 2007" on my website:
www.thecompanyrocks.com/excels

Keep sending in your requests for specific Excel tips. I will create a lesson to solve your problem as soon as my schedule permits.

Danny Rocks

Category:

Howto & Style

Tags:

• Microsoft
• MS
• Excel
• 2007
• Delinquent
• Invoices
• Past-Due
• COUNTIF
• SUMIF
• Functions
• Tutorials
• Lessons
• Time-Savers

[neetuchauhan7]

Hi Danny,

Your videos are quite useful to me n to many others. Just wanna to ask is it possible to calculate YTD (year till date) with the use of sumifs formula.If yes, please help

Thanks!!

[DannyRocksExcels]

@neetuchauhan7

Thank you. I am pleased that you find my videos helpful.

Here is how you can use SUIMIF() to give you a total of Cumulative Sales up through the current date:

1) I like to used "Named Cell Ranges" in my formula. So, I name Column A "Date" and Column B "Sales.

2) In my formula, refer to the current date using the TODAY() Function

3) =SUMIF(Date, ",="&TODAY*(), Sales)

Note the use of "<=" - the quotation marks and also &TODAY() - ampersand.

Danny Rocks

The Company Rocks

[DannyRocksExcels]

@neetuchauhan7

I just posted a YouTube Video lesson to address your question:

The title is "Use SumIf in Excel to Total Year to Date Sales."

Best,

Danny Rocks

The Company Rocks

[peeweecee]

Excellent video! I learned so much in a short period of time. Your pace really is perfect. Thank you.

[DannyRocksExcels]

@peeweecee

Thank you for writing to tell me that you enjoyed my video.

Over the years, I have learned how to "pace" my teaching style so that most people can follow the concept. I appreciate you bringing this to my attention.

Sincerely,

Danny Rocks

The Company Rocks

[friedwanton]

Thanks Danny.. You Rock!!!!!

I am able to use this now..

[DannyRocksExcels]

@friedwanton

Thanks - that make me very happy. I am pleased that I could help you.

Danny Rocks

[neetuchauhan7]

Thanks danny:)



[neetuchauhan7]

Hi Danny,

Is it possible to make YTD formula dynamic with the use of sumifs formula.If yes, Please suggest.

Thanks

[LoniHart]

Using the "now" or" today" i a paragraph with several other funtions that are none date (currency) the format gets buggered improperly Is there a way to format each formula in one cell, (date, currency, text)?

[LoniHart]

Thanks for sharing on how to use count if in excel. I cannot get it to count the following properties with ocean view. If I put count if a2a12,Ocean it only yields one? and work around?

 OCEAN

Ocean View,Water View

Marina View,Ocean View,Panoramic View,Water View

Canyon View,Hills View

Coastline View,Hills View,Ocean View,Panoramic View,View,Water View,White Water View

Catalina Island View,Ocean View,Water View,White Water View

Coastline View,Ocean View,Water View