Look Who it Was Random video

Excel - How to Sum Overdue Invoices using Microsoft Excel | Excel Tutorial - Episode 749

1,174 views · Published 5 February 2009 · 2:54 · Indexed 28 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Sum of Overdue Invoices.

Welcome back to the MrExcel netcast! In today's episode, we'll be tackling a question sent in by Rob. He has a list of power supplies and the dates that the batteries were installed in them. Rob knows that the batteries are good for 5 years, and he has already used conditional formatting to highlight the ones that are overdue for a change. But now, he needs to add up the total number of batteries he needs to order. Let's take a look at his conditional formatting and see how we can help him out.

To highlight the overdue batteries, Rob has used the "Formula Is" version of conditional formatting and put in a formula that checks if the date in column C is greater than 1825 days (which is 5 years). This is a clever use of conditional formatting that not many people know about. But now, we need to build a formula that will add up the corresponding batteries in column B for all the dates that are more than 1825 days old.

To do this, we'll use the SUMIF formula. First, we need to specify the range of dates we want to look at, which is column C. Then, we need to build the criteria, which will change every day. So, we use the concatenation character (&) to add the ＜ sign and our calculation (TODAY()-1825). Finally, we specify the range of batteries we want to sum, which is column B. And voila, we get the total number of batteries that are overdue for a change - 36 in this case.

This is a great example of using conditional formatting and formulas together to solve a problem. Rob's use of conditional formatting was already impressive, and now we have added the SUMIF formula to make it even more efficient. I hope you found this tip useful. Thanks for watching, and don't forget to tune in for more netcasts from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction and question from Rob
(00:20) Explanation of the problem and current solution
(00:43) Overview of the conditional formatting used
(00:53) Explanation of the formula used in the conditional formatting
(01:05) Introduction to the SUMIF formula
(01:18) Building the criteria for the SUMIF formula
(01:52) Final formula and result
(02:02) Testing the formula
(02:14) Summary and conclusion
(02:34) Closing remarks and invitation to the next netcast.

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding up values based on a condition in Excel
Building dynamic criteria for a SUMIF formula in Excel
Calculating date differences in Excel
Calculating the number of batteries needed based on installation dates in Excel
Creating a SUMIF formula in Excel
How to highlight overdue items using conditional formatting in Excel
MrExcel netcast on Excel formulas and formatting techniques
Tips for efficient data analysis in Excel
Understanding the TODAY function in Excel
Using CONCATENATE function in Excel formulas
Using the FORMULA IS option in Excel conditional formatting
YouTube video about conditional formatting in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152140/



Rob has a spreadsheet showing install dates for several batteries. He needs to sum all of the batteries that are overdue for being replaced. This requires a tricky variation of the SUMIF formula. Episode 749 shows you how.

More from this channel