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.
—————-
‘Code created by Sumit Bansal from
‘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
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
—————-
✅ Download File:
✅ Personal Macro Workbook Video:
☕ If you find my Excel videos useful and would like to support me, you can buy me a coffee –
✅ Free Excel Course (Basic to Advanced) –
✅ Free Dashboard Course –
✅ Free VBA course –
✅ Free Power Query Course –
✅ Best Excel Books:
Subscribe to get awesome Excel Tips every week:
#Excel #ExcelTips #ExcelTutorial
