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

If you are facing the VALUE error to fix this click the FX logo next to the formula bar and define the formula from there. Mine did not calculate with comma but it did with doubledot comma.

I just found this video and it is AWESOME! I subscribed. Your explanations are clear and concise. Thank you so much for sharing the coding. I have one question: everything is working except when I enter numbers that have decimals, the formula section is rounding it up/down to a whole number. For instance 3.25 is rounded down in the sum cell to 3.00, but I want it to show the actual 3.25. I have tried just about everything to fix this. What am I missing?

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

Thank you it works in self-coloring cell. But it doesn't works in conditional formatting cell. Do you have any suggestion?

HI,
I can't seem to find option for filter by column. I'm using sharepoint. Is there any way to do it?

Thanks bro!

Thank you , useful video. Summation comes as whole number (rounded). How to change the format to Decimals?

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!

Wonderful video - thank you Sumit!

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

thanks alot but it dont add decimals for me

Great video. When I press F9 the formula doesn’t refresh.

Brilliant - thank-you!

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

Just returning a value of 0. Not calculating. Please help anyone that had the same problem.

What if I just want to count how many cells of a certain colour there are within a range?

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.

For the 3rd method, may I know why is the formula has no.38 specifically? The one =Get.Cell(38,B2)

Thank you so much! This made my day! I love excel and what you can do with it!

This is really an ingenious tool. But I have a little problem with the code. When I use this code rounds to whole euros for example When the sum should actually be 62.18 €, the result in the spreadsheet is 63.00 what is the reason can you help me?

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

Thank you! I followed your explanation and it worked! However, as you said it’s necessary to press F9 to force recalculate. Is it possible to also automate the forced recalculation?

thank you, this VBA code is amazing, i am trying to use the same code to do a 'counta' instead of sum of range of colored cells. Can you advise how to do that please?

How do you add numbers with a decimal and keep the decimal when you add with the colored cell?

Thanks a lot for the sharing!

Thanks.very helpful 😀

why 38 ?

Thank you so much for sharing and your generosity!

Dont Work, get error when Use function.

I'm using the same VBA code but value shows as zero ,
It doesn't sum up by color, can you please suggest.

Thanx

Very good indeed!
Many thanks for elevating our skills sir!

Very informative. Will you pls make a vedio on array used in Dictionary. If you already have pls share the link. Thanks

How can the function sumbycolor be more dynamic so that if you change the background color it re calculates the new range?

Your video was a huge lifesaver!! 🌟🌟 Made my research task much simpler and efficient 🥺 Although I don't understand why VBA wasn't working/able to sum few of different shaded cells and i ultimately had to copy the sums of those columns, would like to know this, if you have any idea.

Ps. Saving this video for future use!!

Thats so usefull, thanks man!

Thank you much sir. Have a great day!

This is truly incredible, thank you! I'm curious as I've put my vba into my document, it's not counting the cells that have conditional formatting coloring, only if it's original to the cell. Any idea how I can update my VBA code to count the cells with conditional formatting?

When I just try to pull what the color index for one of those cells with conditional formatting is, it comes back as -4142 regardless of what color it is.

thnks dude...but how about if those filled color into cell was already done by conditional formatting???i try to code it after conditional formatting (finding the least value and fill the least value with color and leave the greater value as with no color)but it does not recognized the conditional formatting i've done into data i need to manually fill it again before it recognized the cell value..
here is my formula
conditional formatting =I18=MIN($I18:$M18) it will fill the cell having the lowest value
this was my vba code:
Function Sumbycolor(myRange As Range)

For Each myCell In myRange
If myCell.interior.color Then
mySum = mySum + myCell.Value
End If
Next

Sumbycolor = mySum

End Function

it shows no value or zero (0)
thanks in advance

This is great thanks very much, one question this doesn't seem to work for cells that are coloured by conditional formatting do you have a solution for this.

if in a decimal place 0.5 can't count.

Excellent lesson, thank you so much

Hi; Need some help with a formula; Can you help or direct me to the video that yo may have set up Regards Rav

Nice video

Why does nobody use the built-in InteriorColor function? If you enter =InteriorColor(B2), Excel will provide a numeric value for the color in cell B2. You can then use that value to write any formula. I often use an IF formula to copy only cells of a certain color: =IF(InteriorColor(B2)=InteriorColor(F35),F35,"")

This was great! Exactly what I was after.

The figures i'm working with include decimals, I was just wondering what I can do to stop the totals from rounding to .00?

Thx a lot for your work but I cold not get it to run in MacBook. Any suggestions?