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

[@darrenfitzpatrick1513]

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.

[@fairdathm]

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.

[@IvanCortinas_ES]

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

[@trumpexcel]

Thanks Ivan... Glad you found the video useful :)

[@e1dertitan]

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

[@ms.gsimone444]

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

[@Devad2007]

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

[@shehzadiqbal2215]

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

[@nancylopez1708]

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

[@ileanamartinez3265]

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

[@pairenu]

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

[@delta_magoo709]

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!

[@trumpexcel]

Glad you found the video useful 😊

[@pama-g5667]

Thank you! I was having the same issue and your suggestion helped!

[@user-sd4xw9uj8y]

This is exactly what I was looking for. Thanks for the tip on changing the function declaration to Double from Integer and Long.

[@leratolikhomo1649]

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

[@shiffamohammed5818]

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

[@trumpexcel]

Thanks Shiffa... Glad you found the video useful :)

[@wayneedmondson1065]

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!!

[@trumpexcel]

Thanks Wayne... Excel is getting better and better with new formulas, but VBA still adds a lot of value in some scenarios.

[@piyushquicker4330]

@@trumpexcel How did you took the value of 38 in Get.Cell formula.

[@rouellanchesr.4248]

Thank you so much for sharing and your generosity!

[@jayacharya7333]

Thanks Sumit. That would be highly useful.

[@user-qw5ud1wq3t]

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

[@esperanzanitao8118]

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

[@diywithferika216]

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

[@wyograd03]

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

[@Nikki-sm7qg]

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.

[@katrinaralston7701]

I have similar issue. I am trying to add currency figures and it ignores the decimals.

[@ianpengilley5160]

Wonderful video - thank you Sumit!

[@lost0415]

Thanks a lot for the sharing!

[@Anbu_1123]

Thank you much sir. Have a great day!

[@briannjungethairu7957]

Really great, thank you for sharing

[@mohammadilyas2240]

Excellent زبردست

[@markdowell5778]

Brilliant - thank-you!

[@saph39]

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?

[@saph39]

Found the answer below AND now it is perfect!

[@ashokanselliah9571]

Excellent lesson, thank you so much

[@sakisl7739]

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

[@shaniharris-rotohiko1223]

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?

[@allisonschaffer7906]

I'm having the same issue, it is rounding, and I haven't found why.

[@florey112]

I was looking for the same and found a comment earlier has given the solution and it worked perfectly. Wyrade 3 weeks ago Just replace "Dim TotalSum As Long" with "Dim TotalSum As Double", pretty simple.

[@temyraverdana6421]

Thanks, great lesson

[@trumpexcel]

Thanks... Glad you found the video useful :)

[@aaronlindsay7488]

Nice video

[@nadermounir8228]

Nice video 📹 👍 u bring some neat stuff

[@hazemali382]

always great Sumit

[@trumpexcel]

Thanks... Glad you found the video useful :)

[@imranullahsyed7863]

Very informative..

[@isuruhettiarachchi9577]

Thanks.very helpful 😀

[@jessipinkman7659]

Super Sir!

[@kaesuma]

This is really cool Sumit. Thank you!

[@trumpexcel]

Thanks Uma... Glad you found the video useful :)

[@ianrowland7101]

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.

[@mihapremrl1384]

Thats so usefull, thanks man!

[@OriginalKaramelKing]

Thanks bro!

[@chantelleyurechuk9764]

You are Very knowledgeable! :) I bet you know an easy way to do a SUMIF formula up until one column has a name then no name

[@thrpersonalaccount6898]

Hi there. Thanks for helping with this. I have given some of my cells custom colours and am finding this UMF doesn't work on all of them. Do you have any ideas how to fix this please?

[@nickkramer1034]

Great Video! I noticed when I do SumByColor, it rounds up or down to the nearest whole number. Is there a way to modify the script so it goes two decimal places out?

[@Wyrade]

Just replace "Dim TotalSum As Long" with "Dim TotalSum As Double", pretty simple.

[@miikemeezy19]

@@Wyrade Thank you for the note to switch from "Long" to "Double". EZ does it!

[@saph39]

I am SO glad I read through these comments!!! :)

[@n.d.8713]

Thankyou very much, could you please help me some more, i need to have the decimals into it too. But for me, it keeps rounding the numbers to a whole.

[@dan-jq1bw]

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

[@Nikki-sm7qg]

I am looking for the same answer. Have you found a way?

[@user-sd4xw9uj8y]

Great video on how to sum by color cells but I noticed the result generates an integer (decimals are dropped). Any suggestions on how to carry all the decimals in the SumByColor function. Thanks all.

[@user-sd4xw9uj8y]

See the solution @delta_magoo709 posted on how to carry the decimals in the result

[@riosafutra7773]

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

[@alializadeh8195]

Thanx

[@trumpexcel]

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course

[@rtrbs8383]

Thank you Sir....

[@rahulbakshi285]

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

[@kansiwa]

Hi, the video is great and all, but when Isum it up, it deosn't sum to the dot. It will auto round up. How do I sum it up to the decimal point?

[@NicholasMachan]

This is great. I am trying to sum a time based value using this VBA, it currently gives a #VALUE error. Is there an applicable modification I could do to the module? Thanks.

[@stacyhopkinson1570]

This is a great video thank you. I have just set up the VBA code but unfortunately it doesn't work as the numbers that I need to add up contain decimals (0.25, 0.5, 0.75 and 1 etc) do you know a way of making the formula work with the inclusion of decimals? Currently it only works if I remove the decimal and make it a whole number. Thanks

[@poliisinpoika]

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.

[@NehaGahlot]

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!!

[@suviharris3040]

Probably fractions

[@suviharris3040]

Just replace "Dim TotalSum As Long" with "Dim TotalSum As Double",

[@piyushquicker4330]

How did he took the value of 38 in Get.Cell formula?

[@zahidah321]

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

[@stuartl3367]

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

[@user-yj6jb3ez1l]

I have the same question.

[@BLUE14BFB_IH8BFBEDITZ]

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

[@saph39]

I am having this problem too. Did you find a fix? I've tried everything I can think of and Googled several solutions.

[@saph39]

Oh wait - saw the solution below!

[@sarahpom7771]

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?

[@LIUBluejeans]

I love this, and would find it very useful except that the total is rounded to the nearest whole dollar. I work with very precise numbers and I need all my decimal points. Changing the cell format does not fix the problem. The calculation is giving me a round number. How can I fix this?

[@LIUBluejeans]

Nevermind, I fixed my own problem. The answer is to change the data type from "Integer" to Double in the VBA code. Excellent video! Thanks for the wonderful tip.

[@cloud9trauma]

@@LIUBluejeans Hi, I tried changing this in the VBA code but it didnt seem to work. Can you show me how? Thanks

[@nickkramer1034]

@@LIUBluejeans Did not work for me as well. Could you paste the code here. Maybe I am missing something?

[@LIUBluejeans]

@@cloud9trauma, @nick kramer Here's my VBA code: Function SumByColor(SumRange As Range, SumColor As Range) Dim SumColorValue As Double Dim TotalSum As Double 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 If I recall correctly, the only thing I changed was the word "Integer" in the 2nd and 3rd lines to "Double", which gives me back my decimal points. I did find that when changing the data source, or changing data within the source range, I usually have to refresh the cell with the formula by pressing F2 and then enter. (F9 to recalculate does not work for me.) My example in use is: Cell N1, N2, and N3 all contain a SumbyColor formula. Cell N1 is blue, N2 is yellow and N3 is orange. The lookup range is O1:O1000, so the formula reads =SumByColor(O1:O1000,N1). Then, within the source range for this month, Cell O371 is blue. Cells O368, O369, O417, O477, and O591 are orange, and cells O440 to O443 are yellow. The formulas in column N rows 1-3 will then give me the sum of the appropriate colored cells. Each month, I erase the data in the source range, clear the fill colors, and dump a new data query in the same range. Then I color code the appropriate line items, click on the cells where my Sum formulas are located, press F2 (as if to edit), then enter, and voila! Hope that helps. I'm nowhere near the expert Sumit Bansal is, but I'm trying my best 🙂

[@LIUBluejeans]

Even though I posted above in response to cloud9trauma, here's my code that works in my spreadsheet. Function SumByColor(SumRange As Range, SumColor As Range) Dim SumColorValue As Double Dim TotalSum As Double 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

[@martinargimon730]

Hi Summit. Thanks for your video. My understanding is that we cannot use Macro enabled files in the 'On line' version of Excel. In other words no "Activex" controls driving macros work. Is this correct?

[@trumpexcel]

Yeah, that's correct

[@jq8706]

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

[@eminevrankaya3071]

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

[@andrewscoins5013]

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?

[@222AGR]

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.

[@georgedomse]

The VBA does not work for me. Every time I try to use it, I get a popup in Excel, saying "There is a problem with this formula". What do I do wrong?

[@ExcelBytes]

Great tip. I was able to use it when copied to this specific file, but not when I copied it to my Personal Workbook. Any trick to doing that? The function does not come up when I try it in a different workbook.

[@trumpexcel]

When the function is saved in Personal Macro Workbook, you can use it in any workbook but you need to add Personal.xlsb before the formula name. In this example, you can use =PERSONAL.XLSB!SUMBYCOLOR(A1:A4,A1) The formula name doesn't show up when typing, and it's confusing that we need to qualify the function by adding the PERSONAL.XLSB! prefix.

[@Kropus13]

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

[@payaljain5497]

Hi Sumit, I did not understand get.cell function? Could you please explain it again?

[@sachdarade2007]

Need 1 help from u boss... There r multiple no of coloums with random nos.. question is that...hightlight the greather no compare then previous cell..pls help boss note...any random numbers r there in coloum

[@sofimr]

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

[@saikiau2]

why 38 ?

[@trumpexcel]

It's a code Get.Cell function uses to identify the color

[@fasulyedenmail]

Hello in my excel workbook it works once fine. Then when i go out and open the file again, the function always outs #NAME?

[@nagarjunsagara7971]

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

[@rtrbs8383]

Thank you very much ....My Doubt why it is not working and showing "Compile Error - Variable not defined" when Option Explict is active. when I remove Option Explict it it working fine.

[@trumpexcel]

You can use the below code: 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

[@albertmnic]

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?

[@robn_outdoors]

I have the exact same question. Also F9 doesn't work for me. I wonder if there is something I need to turn on for that to work. Only thing that works for me is going to the SumByColor cell, hitting F2, then enter. Then it recalculates. F9 doesn't do anything for me.

[@i7rooper]

I need this buit without VBA code. File needs to be accessed from Web Office

[@triptisharma1738]

Not able to do - compile error : expected : end of statment showing.

[@stephenhughes1764]

This is something I've been trying to do for a while and very often use the SUBTOTAL function until I found your VBA code 😊. However, if I change the cell colour F9 doesn't appear to update the cells with the VBA formula. Any ideas?

[@trumpexcel]

I also tried and you're right, F9 doesn't work, I remember it used to work in the older versions. In this case, the only way I can figure out now would be to get into the edit mode in the cell (by double clicking on it or selecting and then pressing F2) and then hitting enter

[@gopakumartk9199]

👍

[@srinivenkatesh]

Hello Sumit, this Name Manager, Cell Color by Sum is not working and throwing up #NA Error.

[@allisterbaken6089]

Hi I tried this but I get a Comile error Variable not f defined. The Error is on the row Set rCell = SumRange and highlights rCell. Can you help me resolve this Thanks

[@trumpexcel]

Hello Allister.. You can use the below code. I have added a line in the code (Dim rcell As Range), so it would work even when you have option explicit in the module 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

[@martinargimon730]

Thanks for your reply. So if we cannot use Macro enabled files in the 'On line' Excel Version ( which by the way I share lot of documents with MY teams in MS "Teams and Sharepoint' what is the solution ? Is Microsoft ever going to give us an alternative to view and use Excel files with it full potential in the on line version ?. Have you got any inside info if the are working on it ? thanks

[@trumpexcel]

As far as I know, for Excel Online, the plan is to use Java Script as the way to get automation done. Some experts believe MSFT will do away with VBA macros altogether in the future, but there is no time line or confirmation on this. I am assuming Excel Online will be a lot like Google Sheets (which also uses JS for automation)

[@martinargimon730]

@@trumpexcel many thanks for your input. Kind Regards. Martin ( south africa)

[@aece_aeceae4982]

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

[@vizzini2510]

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,"")

[@hugowesley4074]

which version of excel does this work in?

[@rsrrai6475]

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

[@fernandocaceres9930]

Thanks Sumit. I have Microsoft 365 and I can not get function "Get.cell" . Please explain a little abouit it. And the translator does not work

[@trumpexcel]

Hi Fernando... I have recorded this video in Microsoft 365, and I could use the Get.Cell function. Are you getting the BLOCKED error by any chance?

[@fernandocaceres9930]

@@trumpexcel Hi Sumit, thanks. No kind of error. I have Microsoft 365 in Spanish. Do you know this translation please? My translator does not work

[@fernandocaceres9930]

@TrumpExcel Hi Sumit. In addition to the question about Function "get.cell". Please do you know if you can send a image, also?

[@RepZap]

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

[@kurai-kudam166]

get.cell is not working for me

[@user-jb4mh6pb7z]

i tried but i get #NAME? i am new to VBA please help me

[@Colourful_life]

Pl also share countbycolor vba code. Thanks

[@trumpexcel]

'Code created by Sumit Bansal from trumpexcel.com Function GetColorCount(CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Each rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

[@testground7414]

@@trumpexcel How to add help text? It states now (No help avaiable) in Formule Argument Window? Help is highly appreciated!

[@pungskalle]

Dont Work, get error when Use function.

[@teoxengineer]

I also created a new VBA script without Function as below: Option Explicit Sub SumColor() Dim Cell As Range Dim RangeList As Range Dim ColorIndex As Integer Dim Sum1 As Long, Sum2 As Long Sum1 = 0 Sum2 = 0 Set RangeList = Range("b2:b15") For Each Cell In RangeList ColorIndex = Cell.Interior.ColorIndex If ColorIndex = 40 Then Sum1 = Sum1 + Cell.Value ElseIf ColorIndex = 6 Then Sum2 = Sum2 + Cell.Value End If Next Cell Range("b16").Value = Sum1 Range("b17").Value = Sum2 Range("d2").Formula = "=b16" Range("d3").Formula = "=b17" End Sub

[@akulchhillar]

First

[@aminulluddinmansor5387]

if in a decimal place 0.5 can't count.

[@aminulluddinmansor5387]

kindly pleased assist

[@aliyahcurves3101]

thanks alot but it dont add decimals for me