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
@rtrbs83832 жыл бұрын
Thank you Sir....
@e1dertitan Жыл бұрын
I dont usually comment on youtube videos but this deserves a like and comment. Thankyou brother
@darrenfitzpatrick15132 жыл бұрын
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.
@fairdathm2 жыл бұрын
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_ES2 жыл бұрын
Exceptional video Sumit. Thank you very much for your very clear explanations. Surprising the GET.CELL function. That's awesome.
@trumpexcel2 жыл бұрын
Thanks Ivan... Glad you found the video useful :)
@pairenu2 жыл бұрын
Get. Cell 🤗🤗🤗 I used this function 7-8 year back
@Devad20072 жыл бұрын
Another top video by yourself and the Get.Cell was was a surprising function
@ms.gsimone4447 ай бұрын
Thank you so much!! This saved me at least an hour and I appreciate how direct you were!
@shehzadiqbal22152 жыл бұрын
Thank you so much Patel sir. Love and respect from Pakistan
@ileanamartinez326511 ай бұрын
Thank you! This works, I used the VBA for the first time thanks to you :)
@delta_magoo70911 ай бұрын
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!
@trumpexcel11 ай бұрын
Glad you found the video useful 😊
@pama-g566710 ай бұрын
Thank you! I was having the same issue and your suggestion helped!
@user-sd4xw9uj8y8 ай бұрын
This is exactly what I was looking for. Thanks for the tip on changing the function declaration to Double from Integer and Long.
@leratolikhomo16497 ай бұрын
Very clear and helpful. Thank you so so soooo much Sir. Much Love from Lesotho...♥♥♥♥♥♥
@wayneedmondson10652 жыл бұрын
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!!
@trumpexcel2 жыл бұрын
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.
@shiffamohammed58182 жыл бұрын
Thank you so much Sumit, for the superb video as always!!
@trumpexcel2 жыл бұрын
Thanks Shiffa... Glad you found the video useful :)
@esperanzanitao8118 Жыл бұрын
Your SumByColor formula is a life saver! thank you so much for sharing!
@user-qw5ud1wq3t Жыл бұрын
Thank you so much! This made my day! I love excel and what you can do with it!
@rouellanchesr.4248 Жыл бұрын
Thank you so much for sharing and your generosity!
@wyograd03 Жыл бұрын
This was a great video and very helpful! Got exactly what I needed to get done using your method! Just earned a subscriber.
@diywithferika2167 ай бұрын
Great!!! very helpful for my project! Thank you so much.
@nancylopez170810 ай бұрын
Thank You!!! Very easy step by step explanation. Love it!!
@jayacharya73332 жыл бұрын
Thanks Sumit. That would be highly useful.
@mohammadilyas22402 жыл бұрын
Excellent زبردست
@Anbu_1123 Жыл бұрын
Thank you much sir. Have a great day!
@lost0415 Жыл бұрын
Thanks a lot for the sharing!
@ianpengilley516011 ай бұрын
Wonderful video - thank you Sumit!
@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.
@briannjungethairu79572 ай бұрын
Really great, thank you for sharing
@markdowell5778 Жыл бұрын
Brilliant - thank-you!
@ashokanselliah95712 жыл бұрын
Excellent lesson, thank you so much
@temyraverdana64212 жыл бұрын
Thanks, great lesson
@trumpexcel2 жыл бұрын
Thanks... Glad you found the video useful :)
@nadermounir82282 жыл бұрын
Nice video 📹 👍 u bring some neat stuff
@saph3910 ай бұрын
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?
@saph3910 ай бұрын
Found the answer below AND now it is perfect!
@sakisl7739 Жыл бұрын
Very good indeed! Many thanks for elevating our skills sir!
@aaronlindsay74882 жыл бұрын
Nice video
@shaniharris-rotohiko12232 жыл бұрын
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?
@allisonschaffer79062 жыл бұрын
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.
@kaesuma2 жыл бұрын
This is really cool Sumit. Thank you!
@trumpexcel2 жыл бұрын
Thanks Uma... Glad you found the video useful :)
@imranullahsyed78635 ай бұрын
Very informative..
@isuruhettiarachchi9577 Жыл бұрын
Thanks.very helpful 😀
@poliisinpoika9 ай бұрын
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.
@hazemali3822 жыл бұрын
always great Sumit
@trumpexcel2 жыл бұрын
Thanks... Glad you found the video useful :)
@jessipinkman76592 жыл бұрын
Super Sir!
@n.d.87138 ай бұрын
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.
@chantelleyurechuk97642 жыл бұрын
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
@ianrowland71012 жыл бұрын
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.
@thrpersonalaccount68983 ай бұрын
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?
@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?
@georgedomse6 ай бұрын
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?
@OriginalKaramelKing10 ай бұрын
Thanks bro!
@mihapremrl1384 Жыл бұрын
Thats so usefull, thanks man!
@piyushquicker4330Ай бұрын
How did he took the value of 38 in Get.Cell formula?
@rahulbakshi285 Жыл бұрын
Very informative. Will you pls make a vedio on array used in Dictionary. If you already have pls share the link. Thanks
@NicholasMachan4 ай бұрын
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.
@riosafutra777310 ай бұрын
Thank you it works in self-coloring cell. But it doesn't works in conditional formatting cell. Do you have any suggestion?
@jq870611 ай бұрын
Great video. When I press F9 the formula doesn’t refresh.
@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-sd4xw9uj8y8 ай бұрын
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-sd4xw9uj8y8 ай бұрын
See the solution @delta_magoo709 posted on how to carry the decimals in the result
@nickkramer10342 жыл бұрын
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?
@Wyrade2 жыл бұрын
Just replace "Dim TotalSum As Long" with "Dim TotalSum As Double", pretty simple.
@miikemeezy192 жыл бұрын
@@Wyrade Thank you for the note to switch from "Long" to "Double". EZ does it!
@saph3910 ай бұрын
I am SO glad I read through these comments!!! :)
@stacyhopkinson15707 ай бұрын
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
@alializadeh8195 Жыл бұрын
Thanx
@zahidah321 Жыл бұрын
For the 3rd method, may I know why is the formula has no.38 specifically? The one =Get.Cell(38,B2)
@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?
@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?
@sofimr Жыл бұрын
What if I just want to count how many cells of a certain colour there are within a range?
@nagarjunsagara7971 Жыл бұрын
I'm using the same VBA code but value shows as zero , It doesn't sum up by color, can you please suggest.
@Kropus1310 ай бұрын
HI, I can't seem to find option for filter by column. I'm using sharepoint. Is there any way to do it?
@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?
@stuartl3367 Жыл бұрын
How can the function sumbycolor be more dynamic so that if you change the background color it re calculates the new range?
@user-yj6jb3ez1l6 ай бұрын
I have the same question.
@LIUBluejeans2 жыл бұрын
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?
@LIUBluejeans2 жыл бұрын
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.
@cloud9trauma2 жыл бұрын
@@LIUBluejeans Hi, I tried changing this in the VBA code but it didnt seem to work. Can you show me how? Thanks
@nickkramer10342 жыл бұрын
@@LIUBluejeans Did not work for me as well. Could you paste the code here. Maybe I am missing something?
@LIUBluejeans2 жыл бұрын
@@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 🙂
@LIUBluejeans2 жыл бұрын
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
@BLUE14BFB_IH8BFBEDITZ10 ай бұрын
Thank you , useful video. Summation comes as whole number (rounded). How to change the format to Decimals?
@saph3910 ай бұрын
I am having this problem too. Did you find a fix? I've tried everything I can think of and Googled several solutions.
@saph3910 ай бұрын
Oh wait - saw the solution below!
@payaljain54972 жыл бұрын
Hi Sumit, I did not understand get.cell function? Could you please explain it again?
@RepZap Жыл бұрын
Just returning a value of 0. Not calculating. Please help anyone that had the same problem.
@eminevrankaya30712 жыл бұрын
Thx a lot for your work but I cold not get it to run in MacBook. Any suggestions?
@i7rooper2 жыл бұрын
I need this buit without VBA code. File needs to be accessed from Web Office
@teoxengineer2 жыл бұрын
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
@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",
@sachdarade20072 жыл бұрын
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
@triptisharma17382 жыл бұрын
Not able to do - compile error : expected : end of statment showing.
@saikiau2 Жыл бұрын
why 38 ?
@trumpexcel Жыл бұрын
It's a code Get.Cell function uses to identify the color
@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.
@ExcelBytes2 жыл бұрын
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.
@trumpexcel2 жыл бұрын
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.
@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
@rtrbs83832 жыл бұрын
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.
@trumpexcel2 жыл бұрын
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
@srinivenkatesh2 жыл бұрын
Hello Sumit, this Name Manager, Cell Color by Sum is not working and throwing up #NA Error.
@martinargimon7302 жыл бұрын
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?
@trumpexcel2 жыл бұрын
Yeah, that's correct
@rsrrai64752 жыл бұрын
Hi; Need some help with a formula; Can you help or direct me to the video that yo may have set up Regards Rav
@vizzini25102 жыл бұрын
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?
@allisterbaken60892 жыл бұрын
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
@trumpexcel2 жыл бұрын
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
@kurai-kudam1662 жыл бұрын
get.cell is not working for me
@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_outdoors8 ай бұрын
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.
@user-jb4mh6pb7z Жыл бұрын
i tried but i get #NAME? i am new to VBA please help me
@pungskalle Жыл бұрын
Dont Work, get error when Use function.
@stephenhughes17642 жыл бұрын
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?
@trumpexcel2 жыл бұрын
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
@fernandocaceres99302 жыл бұрын
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
@trumpexcel2 жыл бұрын
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?
@fernandocaceres99302 жыл бұрын
@@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
@fernandocaceres99302 жыл бұрын
@TrumpExcel Hi Sumit. In addition to the question about Function "get.cell". Please do you know if you can send a image, also?
@martinargimon7302 жыл бұрын
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
@trumpexcel2 жыл бұрын
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)
@martinargimon7302 жыл бұрын
@@trumpexcel many thanks for your input. Kind Regards. Martin ( south africa)
@Colourful_life2 жыл бұрын
Pl also share countbycolor vba code. Thanks
@trumpexcel2 жыл бұрын
'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
@testground74142 жыл бұрын
@@trumpexcel How to add help text? It states now (No help avaiable) in Formule Argument Window? Help is highly appreciated!