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

preview_player
Показать описание
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
Рекомендации по теме
Комментарии
Автор

Exceptional video Sumit. Thank you very much for your very clear explanations. Surprising the GET.CELL function. That's awesome.

IvanCortinas_ES
Автор

Another top video by yourself and the Get.Cell was was a surprising function

Devad
Автор

Your video was so clear, thank you! I had followed instructions to set up a SumByColor that failed to mention the need to select the tab to apply the module. Now that I've done that, thanks to your video, it works perfectly.

fairdathm
Автор

Thank you so much!! This saved me at least an hour and I appreciate how direct you were!

ms.gsimone
Автор

Thank You!!! Very easy step by step explanation. Love it!!

nancylopez
Автор

I dont usually comment on youtube videos but this deserves a like and comment. Thankyou brother

edertitan
Автор

You are my hero simple VBA is the best! My work love colour coding spreadsheets and this made life so much easier. A note for anyone who isn't summing a continuous range you can put the range e.g. A2, D2, G2 inside it's own parenthesis within the the sumbycolour function and it will work.

darrenfitzpatrick
Автор

Thank you so much Sumit, for the superb video as always!!

shiffamohammed
Автор

Thank you - very clear and exactly what I was looking for. I wish I'd seen this long ago. You are a very good instructor! Subscribed. I needed the result to two decimal places so used Double instead of Integer and Long. Thanks to those suggestions to others below!

delta_magoo
Автор

Your SumByColor formula is a life saver! thank you so much for sharing!

esperanzanitao
Автор

Thank you so much Patel sir.
Love and respect from Pakistan

shehzadiqbal
Автор

Thank you! This works, I used the VBA for the first time thanks to you :)

ileanamartinez
Автор

I love the video and all your help with excel. How do I get this to add up decimals? It keeps rounding everything up or ignoring if its too low to round.

Nikki-smqg
Автор

Thanks Sumit. That would be highly useful.

jayacharya
Автор

Very clear and helpful. Thank you so so soooo much Sir. Much Love from Lesotho...♥♥♥♥♥♥

leratolikhomo
Автор

Thanks Sumit! Love that UDF. LAMBDAs are all the rage right now, but VBA can still do things that LAMBDAs can't, such as your sum by color example. Thanks for sharing the code. Thumbs up!!

wayneedmondson
Автор

Get. Cell 🤗🤗🤗 I used this function 7-8 year back

pairenu
Автор

This was a great video and very helpful! Got exactly what I needed to get done using your method! Just earned a subscriber.

wyograd
Автор

Thank you so much for sharing and your generosity!

rouellanchesr.
Автор

Great!!! very helpful for my project! Thank you so much.

diywithferika