Adam liked my Filter/Sort by color trick on the recent CFO Magazine webcast. He asks if there is any way to sum by color. In Episode #1415, Bill shows us a macro to do this. "Learn Excel 2007 through Excel 2010 From MrExcel" - by Bill Jelen.
Transcript of the video:
Hey, what's Friday?
That means we should be having a dueling Excel podcast.
You know Mike, if you saw on YouTube, had a pretty bad accident-- he tripped and broke his wrist.
He's been having typing problems.
He'll be back next week though, so for this Friday, just doing a regular podcast.
Learn Excel from MrExcel Podcast, Episode 1415: Sum Red.
Well, hey, welcome back to the MrExcel Podcast, I'm Bill Jelen, and today's question's sent in by Adam.
Adam was actually a member of the audience in my CFO Magazine Webcast, talking about macros, and-- just-- it wasn't a macro thing, I just happened to mention during that webcast that you could filter by color.
So, filter by color and choose the font color and all of this.
He said, "Hey, that was a pretty cool trick.
Is there any way to sum by color?
And this isn't [ Inaudible at 00:00:56 ] there's no native way to sum anything in color, but hey, it was a webcast about macros so of course I don't feel bad giving a macro answer.
Let's say we wanted to sum all the red cells in this range, the first thing I have to do is figure out what color that really is.
So I'm going to do a couple of key strokes here-- I'm going to do Alt+F11 and then Ctrl+G.
Ctrl+G brings up the immediate window.
I'm going to say Print, activecell.Font.Color then I'm going to say that cell is 342-- how am I going to remember that?
Okay, we'll come back to that one.
I'll make this a little smaller, and then what we're going to do is we're going to insert a new module.
So Insert, Module, and I'm going to create a little function here.
It's going to be called Function SumRed and we have to tell it which cells we want to look at, so I'm going to say MyRange as Range.
It's important to say as Range-- that way it knows that it's not just passing a value, it's passing a range of cells.
And then what we're going to do is we're going to say, for each cell in MyRange If cell.font.color = --and I have the stuff still down here in the immediate window, I'll copy that, Ctrl+C, paste, then OK, so that's true.
Then we need to add the cell to the total.
Now, the total is going to be a variable called SumRed.
It has to be called Sum.Red because that's the name of the function.
Now, I really should initialize this up here in SumRed = 0, and we can say SumRed = SumRed + cell.Value.
Alright?
End If and then Next cell.
Say, OK.
Well, now how do we return that back to the spreadsheet?
That's the beautiful thing-- because it's called SumRed, that automatically gets written back out to the spreadsheet.
Alright?
Now let's try this.
We're going to File, Close and return to Microsoft Excel, and we'll say = SUMRED, add a little bit of range there, BAM!
205, let's just do a little test.
Up another range or two.
BAM, BAM, BAM, BAM, down at the bottom, 255, that works great.
Now, what if you want to use fill color instead?
You wanted to sum the orange?
Now, let's switch back to VBA-- Alt+F11-- we'll do Function SumOrangeFill and so here we need to change the name of that, and this time, instead of activeCell.Font.Color, we ask for activeCell.Interior.Color.
Now, I need to make sure that I'm in the right cell.
Let's see, choose one of these orange cells, press Enter.
Okay, so, that's a different code.
So we say, If cell.Interior.Color = Then SumOrangeFilled = SumOrangeFill + that cell's Value.
Let's try that.
SUMORANGEFILL, this whole range.
203, which should be the sum of the orange themselves.
Okay, so if you just had up to 93-- if you just had a quick and dirty need-- you could knock out a tiny little macro like that, you know.
But what would be much more flexible is to write a function that can handle any color.
And so I created this function called Sum Color, and the way that it works is, the first thing you have to specify is what color you're trying to add up.
So if I want all of the red cells, for example, and then the range you want to sum-- so that should be 45, which is 45, that's great.
Now, if you wanted to use a fill color, there's an optional third argument-- [ Inaudible 00:05:08 ] by fill, you would use True there, and that will sum up all the yellow sales.
Alright, so let's take a look at that code.
Here, I'm parsing three arguments: SampleColor As Range, RangeToSum As Range, and then this is a continuation character here.
An optional argument, ByFill As Boolean.
Now, one really important thing here with all of these functions-- if the color is going to be changing you have to put Application.Volatile in there, so that way it'll recalculate with the rest of the spreadsheet, otherwise Excel doesn't know that this is something that has to be recalculated.
It won't be in the calculation chain.
And then check to see whether we're looking for either the interior or the font and then loop through.
And on each cell, either check to see if it's equal to the interior or the font and then add it up.
So there's a much more flexible function and easier way to go.
Well, hey, I want to thank Adam for sending that question in, I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
That means we should be having a dueling Excel podcast.
You know Mike, if you saw on YouTube, had a pretty bad accident-- he tripped and broke his wrist.
He's been having typing problems.
He'll be back next week though, so for this Friday, just doing a regular podcast.
Learn Excel from MrExcel Podcast, Episode 1415: Sum Red.
Well, hey, welcome back to the MrExcel Podcast, I'm Bill Jelen, and today's question's sent in by Adam.
Adam was actually a member of the audience in my CFO Magazine Webcast, talking about macros, and-- just-- it wasn't a macro thing, I just happened to mention during that webcast that you could filter by color.
So, filter by color and choose the font color and all of this.
He said, "Hey, that was a pretty cool trick.
Is there any way to sum by color?
And this isn't [ Inaudible at 00:00:56 ] there's no native way to sum anything in color, but hey, it was a webcast about macros so of course I don't feel bad giving a macro answer.
Let's say we wanted to sum all the red cells in this range, the first thing I have to do is figure out what color that really is.
So I'm going to do a couple of key strokes here-- I'm going to do Alt+F11 and then Ctrl+G.
Ctrl+G brings up the immediate window.
I'm going to say Print, activecell.Font.Color then I'm going to say that cell is 342-- how am I going to remember that?
Okay, we'll come back to that one.
I'll make this a little smaller, and then what we're going to do is we're going to insert a new module.
So Insert, Module, and I'm going to create a little function here.
It's going to be called Function SumRed and we have to tell it which cells we want to look at, so I'm going to say MyRange as Range.
It's important to say as Range-- that way it knows that it's not just passing a value, it's passing a range of cells.
And then what we're going to do is we're going to say, for each cell in MyRange If cell.font.color = --and I have the stuff still down here in the immediate window, I'll copy that, Ctrl+C, paste, then OK, so that's true.
Then we need to add the cell to the total.
Now, the total is going to be a variable called SumRed.
It has to be called Sum.Red because that's the name of the function.
Now, I really should initialize this up here in SumRed = 0, and we can say SumRed = SumRed + cell.Value.
Alright?
End If and then Next cell.
Say, OK.
Well, now how do we return that back to the spreadsheet?
That's the beautiful thing-- because it's called SumRed, that automatically gets written back out to the spreadsheet.
Alright?
Now let's try this.
We're going to File, Close and return to Microsoft Excel, and we'll say = SUMRED, add a little bit of range there, BAM!
205, let's just do a little test.
Up another range or two.
BAM, BAM, BAM, BAM, down at the bottom, 255, that works great.
Now, what if you want to use fill color instead?
You wanted to sum the orange?
Now, let's switch back to VBA-- Alt+F11-- we'll do Function SumOrangeFill and so here we need to change the name of that, and this time, instead of activeCell.Font.Color, we ask for activeCell.Interior.Color.
Now, I need to make sure that I'm in the right cell.
Let's see, choose one of these orange cells, press Enter.
Okay, so, that's a different code.
So we say, If cell.Interior.Color = Then SumOrangeFilled = SumOrangeFill + that cell's Value.
Let's try that.
SUMORANGEFILL, this whole range.
203, which should be the sum of the orange themselves.
Okay, so if you just had up to 93-- if you just had a quick and dirty need-- you could knock out a tiny little macro like that, you know.
But what would be much more flexible is to write a function that can handle any color.
And so I created this function called Sum Color, and the way that it works is, the first thing you have to specify is what color you're trying to add up.
So if I want all of the red cells, for example, and then the range you want to sum-- so that should be 45, which is 45, that's great.
Now, if you wanted to use a fill color, there's an optional third argument-- [ Inaudible 00:05:08 ] by fill, you would use True there, and that will sum up all the yellow sales.
Alright, so let's take a look at that code.
Here, I'm parsing three arguments: SampleColor As Range, RangeToSum As Range, and then this is a continuation character here.
An optional argument, ByFill As Boolean.
Now, one really important thing here with all of these functions-- if the color is going to be changing you have to put Application.Volatile in there, so that way it'll recalculate with the rest of the spreadsheet, otherwise Excel doesn't know that this is something that has to be recalculated.
It won't be in the calculation chain.
And then check to see whether we're looking for either the interior or the font and then loop through.
And on each cell, either check to see if it's equal to the interior or the font and then add it up.
So there's a much more flexible function and easier way to go.
Well, hey, I want to thank Adam for sending that question in, I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
