filmov
tv
Sum Cells Based on Their Color in Excel (Formula & VBA)

Показать описание
In this video, I will show you three different methods to add cells based on color.
00:00 Intro
00:25 SUM by Color Using SUBTOTAL Function
02:05 SUM by Color Using VBA Function
08:27 SUM by Color Using Get.CELL + Named Range Trick
If you're looking for an easy one-time method, use the first SUBTOTAL method. If you need to do this quite often and are comfortable with VBA, you can create your own custom function that will give you the sum of cells based on the color in it.
And if you don't prefer VBA, then you can use the third method which uses an old GET.CELL formula trick. It's not the best way to do this, but it gets the work done.
----------------
'This VBA code created a function that can be used to sum cells based on color
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
Dim rcell As Range
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function
----------------
#Excel #ExcelTips #ExcelTutorial
00:00 Intro
00:25 SUM by Color Using SUBTOTAL Function
02:05 SUM by Color Using VBA Function
08:27 SUM by Color Using Get.CELL + Named Range Trick
If you're looking for an easy one-time method, use the first SUBTOTAL method. If you need to do this quite often and are comfortable with VBA, you can create your own custom function that will give you the sum of cells based on the color in it.
And if you don't prefer VBA, then you can use the third method which uses an old GET.CELL formula trick. It's not the best way to do this, but it gets the work done.
----------------
'This VBA code created a function that can be used to sum cells based on color
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
Dim rcell As Range
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function
----------------
#Excel #ExcelTips #ExcelTutorial
Комментарии