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.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today’s question is sent in by Rob.
Rob has a list of power supplies and the dates that the batteries were installed in the power supply.
He says, hey, the batteries are good for 5 years.
I've already gone through and created conditional formatting -- and we'll take a look at that -- to highlight the ones that are overdue, due to be changed, in red, but now I need to add up column B. I need to see how many total batteries I need to order, basically, and let's take a look at his conditional formatting.
It's pretty cool.
FORMAT, CONDITIONAL FORMATTING.
He uses the FORMULA IS version of conditional formatting instead of CELL VALUE IS, puts a little formula in there.
=TODAY – the date in C10 and checks to see if that's greater than 1825?
Why 1825?
Well, remember that the batteries are good for 5 years, so =5*365 is 1825 days.
So, I guess the question is, can we build a SUMIF formula down here that will look through column C, look for dates that are more than 1825 days old, and then add up the corresponding batteries in column B?
[ =TODAY()-C10>1825 ] So, we use =SUMIF.
First thing it wants to know is, what range are we going to look at?
Well, that's the range of dates, so we'll choose C22 up to C2, and then the criteria.
Well, we're going to have to build the criteria here because it's going to change every single day.
So, in “, I’m going to put a < sign and then the concatenation character, the &, and now I put my calculation.
So, we use TODAY, () – 1825, a , to finish the criteria, and then finally we have to put what we're going to SUM.
So, that'll be the corresponding range from column B, so B2 to B22.
Put that in and we get 36.
[ =SUMIF(C2:C22,“<”&TODAY()-1825,B2:B22) ] Let's just do a quick test and make sure that works.
If I highlight all of the red items today, CONTROL click, we should be able to look down on the status bar and see the total of all those items.
So, down here, sure enough, we have 36.
It's working well.
So, this is pretty cool.
A couple of things.
Rob already had a nice use of conditional formatting using FORMULA IS -- not many people ever discover that -- and then, here, we use the SUMIF formula, but what was unusual was that we actually built the criteria on the fly by concatenating the < sign and then our formula.
Well, hey.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
Today’s question is sent in by Rob.
Rob has a list of power supplies and the dates that the batteries were installed in the power supply.
He says, hey, the batteries are good for 5 years.
I've already gone through and created conditional formatting -- and we'll take a look at that -- to highlight the ones that are overdue, due to be changed, in red, but now I need to add up column B. I need to see how many total batteries I need to order, basically, and let's take a look at his conditional formatting.
It's pretty cool.
FORMAT, CONDITIONAL FORMATTING.
He uses the FORMULA IS version of conditional formatting instead of CELL VALUE IS, puts a little formula in there.
=TODAY – the date in C10 and checks to see if that's greater than 1825?
Why 1825?
Well, remember that the batteries are good for 5 years, so =5*365 is 1825 days.
So, I guess the question is, can we build a SUMIF formula down here that will look through column C, look for dates that are more than 1825 days old, and then add up the corresponding batteries in column B?
[ =TODAY()-C10>1825 ] So, we use =SUMIF.
First thing it wants to know is, what range are we going to look at?
Well, that's the range of dates, so we'll choose C22 up to C2, and then the criteria.
Well, we're going to have to build the criteria here because it's going to change every single day.
So, in “, I’m going to put a < sign and then the concatenation character, the &, and now I put my calculation.
So, we use TODAY, () – 1825, a , to finish the criteria, and then finally we have to put what we're going to SUM.
So, that'll be the corresponding range from column B, so B2 to B22.
Put that in and we get 36.
[ =SUMIF(C2:C22,“<”&TODAY()-1825,B2:B22) ] Let's just do a quick test and make sure that works.
If I highlight all of the red items today, CONTROL click, we should be able to look down on the status bar and see the total of all those items.
So, down here, sure enough, we have 36.
It's working well.
So, this is pretty cool.
A couple of things.
Rob already had a nice use of conditional formatting using FORMULA IS -- not many people ever discover that -- and then, here, we use the SUMIF formula, but what was unusual was that we actually built the criteria on the fly by concatenating the < sign and then our formula.
Well, hey.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
