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.

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

2 жыл бұрын

Thank you Sir....

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.

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

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

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

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

@trumpexcel

2 жыл бұрын

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

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

Thank you so much for sharing and your generosity!

Really great, thank you for sharing

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

2 жыл бұрын

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

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

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

Thanks Sumit. That would be highly useful.

Wonderful video - thank you Sumit!

Excellent lesson, thank you so much

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

@trumpexcel

2 жыл бұрын

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

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

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

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

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

Thank you much sir. Have a great day!

Thanks a lot for the sharing!

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

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

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

Brilliant - thank-you!

Very informative..

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

9 ай бұрын

Glad you found the video useful 😊

@pama-g5667

9 ай бұрын

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

@user-sd4xw9uj8y

6 ай бұрын

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

Thanks.very helpful 😀

This is really cool Sumit. Thank you!

@trumpexcel

2 жыл бұрын

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

Nice video 📹 👍 u bring some neat stuff

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.

Super Sir!

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.

Thanks, great lesson

@trumpexcel

2 жыл бұрын

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

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.

Excellent زبردست

Thats so usefull, thanks man!

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?

always great Sumit

@trumpexcel

2 жыл бұрын

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

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

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

Nice video

Thanks bro!

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

2 жыл бұрын

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.

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

8 ай бұрын

Found the answer below AND now it is perfect!

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

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?

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.

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

6 ай бұрын

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.

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

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

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

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

2 жыл бұрын

Yeah, that's correct

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

Thanx

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

2 жыл бұрын

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.

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

8 ай бұрын

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

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

2 жыл бұрын

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

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.

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

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

6 ай бұрын

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

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?

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

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.

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?

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

@saph39

8 ай бұрын

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

@saph39

8 ай бұрын

Oh wait - saw the solution below!

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

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

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

@user-yj6jb3ez1l

4 ай бұрын

I have the same question.

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

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

2 жыл бұрын

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

2 жыл бұрын

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

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

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?

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

2 жыл бұрын

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

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

👍

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

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

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

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

2 жыл бұрын

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

2 жыл бұрын

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

@nickkramer1034

2 жыл бұрын

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

@LIUBluejeans

2 жыл бұрын

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

2 жыл бұрын

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

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

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

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?

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

2 жыл бұрын

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

Pl also share countbycolor vba code. Thanks

@trumpexcel

2 жыл бұрын

'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

2 жыл бұрын

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

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

get.cell is not working for me

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?

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

2 жыл бұрын

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

2 жыл бұрын

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

2 жыл бұрын

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

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

thanks alot but it dont add decimals for me

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

why 38 ?

@trumpexcel

Жыл бұрын

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

Dont Work, get error when Use function.

if in a decimal place 0.5 can't count.

@aminulluddinmansor5387

Жыл бұрын

kindly pleased assist

First