Это видео недоступно.
Сожалеем об этом.
How to Sum Cells by Color in Excel (Get it to update automatically)
HTML-код
- Опубликовано: 14 авг 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Learn how to write an Excel formula to sum values based on the cell background color.
⬇️Download the workbook here: pages.xelplus....
We achieve this with a simple Excel VBA function. The VBA function has two arguments. One is for color and the other the range that needs to be summed. You can use a similar technique to count cells based on color.
We also improve on the VBA code to have the formula results update automatically when a cell color in the range changes. This is done by using the worksheet selection change event for a specific range. We achieve this with the Application.Intersect method.
🔍 What You'll Learn:
- Crafting a custom VBA function for summing values by cell color.
- Simplified approach to VBA programming with hands-on examples.
- How to implement dynamic cell color selection in your custom function.
🛠️ Custom Function Features:
- Ability to sum values based on specified cell background color.
- Two argument implementation: target cell color and range for summing.
- Step-by-step guide to creating and integrating the function in Excel.
💡 Key Takeaways:
- Enhance your Excel projects with dynamic, color-based calculations.
- Gain practical knowledge in VBA, making your data analysis more efficient.
- Discover how to tailor Excel functions to your specific needs.
LINK to Excel VBA Playlist: • Excel VBA & Macros Tut...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #ExcelVBA
Grab the file I used in the video from here 👉 pages.xelplus.com/sum-color-file
Great Video!!! This is what I've been searching for everywhere.
it is just not what you teach, it is also how you present the knowledge to in front of us.
I admire your way of teaching.
I am only at 16% progress of your online course and already feel that finally I am getting the concept and structure of the Vba.
thnx for everyrhing.
Thanks for the kind words. That's good progress on VBA! The beginning is the hardest part.
Great lessons! Thank you!
Best vid ever in this subject. I appreciate you for sharing this. Merci Leila :)
You are making VBA understandable even for me. That's some gift!
I'm happy to hear that :)
Mam in every video you share miracle tricks, which not got on you tube. Love you mam...
I'm happy to hear that the videos are useful.
your way of explanation makes me to get in to excel and learn more and more. Last two years i have been continuously using various ideas from this site. Thanks a lot.
You're most welcome. Thank you for your ongoing support.
Wow....!! I have been using excel since 1997 but I wasn't aware of these fantastic function.
You are just brilliant.
I subscribe already....
I Love You...Mam. I am impressed so much.
Many Many Thanks for sharing.......
You're very welcome. Glad you found something new.
As great as usual. Leila you are Queen of Excel.
Thank you so much for this tutorial.
Kind regards
My pleasure. Glad you like the video.
Great video Leila !! Thanks for sharing !! Ctr+Shift+A is a very helpful shortcut do display the fuction arguments quickly.....
You're very welcome Victor. Glad you like the video.
You are my Excel Guru...🙂 Thanks!
🙂🙂🙂
Thank you SO MUCH 🙏🏼
Flawless and thorough example of something that's been driving me nuts! Thank you so much!
Glad it helped, John!
I have been using sum base on color for sometime but you have added something new. Thanks a million.
You're very welcome Daniel.
An absolutely new aspect of Excel
Never had an idea for any such possibility in Excel
I am very much impressed
Thank you
You're very welcome. That's the beauty of Excel. A lot of possibilities.
@@@LeilaGharani
How to know where to use Vlookup, Index/Match/Dget?
Pls Help
That's easy: always Index & Match :)
I didn't even know this was possible despite using Excel for a long time. Many thanks. :)
I'm glad you found something new.
Once again came with great work, u have made many of us rich & richer knowledge-wise. Just waiting for the next one very soon
I'm happy the tutorial is helpful for you.
Thanks once again Leila. May the good Lord richly bless
You're very welcome Robert.
Great value, finally I understand fantastic metod, I'm using Excel in spanish and it worked perfectly. Like for sure 👍🏼
Thanks very helpful video.
Thank you for your efforts it's the best and very helpfull lesson.
Fabulous tutorial - Thank You. I'm not sure I totally understood the VBA (just bits of it), but I followed your instructions and my spreadsheet works perfectly. So chuffed.
Great job!
thanks again Leila for your best tutorials.
You're very welcome.
Thanks so much for the easy-to-follow, professional video! Works like a dream on my Macbook. :)
Dear Leila.. the kind of knowledge and the skills you have to spread that knowledge is simply awesome.. thanks for all your knowledge sharing.. im a big fan of yours.. sincerely..
One of the best tutor ever
Aww, thank you!
super helpful this video. I had no idea how to create a custom function... also all these little details getting the active cell and a cell's colour.
thanks muchly :)
Hello Leila, I'm a new subscriber!! Thank you for the content you put out! You are super AWESOME!!!!
Great to have you here José. I'm glad you like the content.
Superb..!!! I feel Excel Team should bring this function permanently in workbook. Thanks for sharing solution..!!
You're very welcome. Glad you like it.
Thanks you so much. This is what i needed for a company project. Very clear and very useful! Considering following the VBA course now!
Great to hear! I hope you'll like the course.
Awesome video, competent and fast. Thx a lot!
Huge thanks Leila. Your video tutorial is great and very informative and helpful. Thanks a lot.
You're very welcome Amilcar. Glad you like the video.
@@LeilaGharani Yeah. You've done great work by sharing your expertise. Really appreciate. Keep it up.
If i could like this twice I would. So brilliant and straightforward! Thank you!
My pleasure. Thanks for the Like!
Great video!!
Thank you Leila for letting us know about a new thing today.
You're very welcome. Glad it is helpful.
Hi Leila,
Your tutorials are great . The script does not seem to work with conditional formatting colors. Do you have any idea how we can fixed that? If you can help us on this , you'll be a life saver.
Thank you .
Good Laxcering, Vary Usfoul., Thank you.
Thank you Leila. Excellent as allways.
You're very welcome Enrique. Glad you like it.
Excellent, Very useful. Thanks.
Super erklärt!
Vielen Dank!
You truly are the excel goddess!!!
😘
Thank you. This was amazingly useful. Simple tool to make and edit preliminary Gantt charts.
Thank you ,this is just what I was looking for .
Hello Leila! am Rashid from Pakistan and these days i am practicing vba excel and i enrolled in your vba course on Udemy approx. one and half month ago
Fantastic video: I think, assigning color to variable and than checking will have some overhead if multiple cells have this function.
I would go with directly adding it under if condition because checking Boolean is faster than 2 step process of assigning than checking it.
If cell.interior.color = matchColor.interior.color then....
will be faster.
Thank you for your time Leila.
Good idea. Thank you!
No one like you, you are great
Thank you for the kind words!
Really helpful, Thanks
This is extremely helpful! Thanks Leila!
Thanks a lot Leila the video and trick was awesome you are a great teacher. Even though I am Lecturer myself I find your training and videos amazing. Thanks a lot.
Thanks for the kind words. I'm glad you like the video.
thank you so much for taking part of your time to help other, may you find reward in this life and the next!!!
you are brilliant
You're very welcome. As long as it's helpful I am happy :)
Thank you Leila, That was amazing and easy to understand. Very helpful
Thank for this Leila!
This was very helpfully in calculate my everyday Transactions Records working as wakala in Tz. So happy i found this.
thanks very use full
Came here for Excel and loved the video as I do your other videos. Comments are weird though
Great to have you here.
it seems to be very useful even when dealing with a data matrix containing data where I want to show sum of values in different colored cells for each row... can't wait to check this out, have to clean my data first
I love it great Tuto thanks a lot Leila
Very useful, thanks for sharing
Thank you, this is what i have been looking for a long time. It is really helpful. Big Thank
You're very welcome. I'm glad the video is useful.
I have checked some of your tutorials ...Amazing!!!
I'm glad you like the tutorials.
Thank you very much
Hi Leila. Thank you for the video. I've done the selection update as per your tutorial. The numbers are still not updated. I've changed your VBA slightly to count instead of sum by the way.
I was struggling to do this very thing last week. Thank you.
You're very welcome Michael. Glad I could help with that.
Thanks Leila. Seems like I got to learn VBA for custom needs.
It can really be helpful Peter.
@@LeilaGharani so I thought. Thank you as always. God grant you abundant energy to do the beautiful work you do here (online). I must say that I am your loyal student and has in return referred quite a number who feels the same. I have been a teacher and so can tell of a good one. You are one of the best. Thanks once again and God bless.
You are an expert! 👍
Seems very precise and easy, of-course very well explained but had no luck still; getting error in the function.
Hi Leila, I'm one of your video subscribers, please, I would like to request that you create me an IF or conditional formatting formula to color only one cell that has a total value. Thanks.
Great tutorial. Everything works fine. Having trouble with the formula automatically updating. I set it up following the instructions and everything works. If I save the document excel stops automatically updating the formula if I make any changes. I have to manually force the cell to update. Has anyone else experienced this if know how I fix this problem?
I am have the same problem does any one know how to fix this
I had the same issue, found this on Google and it worked for me. Just added 2 lines to Leila's code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:C")) Is Nothing Then
ActiveSheet.Calculate
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
End If
End Sub
@@mvohra Thank you for the help. It works
@@mvohra OMG, thank you so much
Another great video tip. Thank you.
You're very welcome Dennis. Glad you like the video.
Very nice.
We need VBA lessons.
I'm glad to hear that.
This video is amazing! thank you so much!!! I needed this so much to make my budgets better :) thank you!!!!
Wow thank you, idk anything about VBA but I was able to follow along easily. You are amazing
Excellent as always!
Thank you. Glad you like it.
Truly appreciate your work. Thank you very much
You're very welcome. Glad you find the videos helpful.
Thanks again for a great presentation as usual
You're very welcome. Glad you like it Nissim.
Superb! The Queen of excel really.
I'm glad you like it :)
Great presentation on a function I thought was not possible, but having trouble thinking where this would be useful.
Maybe at some point it will come in handy. It's always good to know what is possible.
Thank you 😊
Hi Leila.. love that If Not Intersect() Is Nothing construct. Before understanding that concept, I used the overkill method of ActiveSheet.Calculate on the full Worksheet_SelectionChange event.. which made some of my workbooks very slow. Now, they are much speedier and more efficient. Thanks for the great videos, lessons and insights.. always something fun, new and interesting on your channel and web site. Thumbs up!!
Thank you for the kind words and the thumbs up Wayne!
Great stuff Leila 👏
I'm glad you like it Heiko.
Thanks Leila, this was a helpful video
You're welcome. I'm glad you like the video.
I have become a huge fan on how you help us and better ways to organise and visualise our data. Unfortunately while this is great when dealing with smaller ranges of data, excel 's performance is really letting me down when it comes down to dealing a large number of rows and references across multiple sheets. Any tip on how to boost the performance in corporate license environment would really help make the most out your tutorials! Great channel! Keep it up.
You could check out this infographic: www.xelplus.com/speed-up-your-excel-files/
Wow. This was amazing thanks!!!
I tried this and it's working. Thank you very much. T.T
Glad it helped!
This is excellent, but is it possible to count instead of sum? Also, can it be set up to count conditionally formatted color? Any help is appreciated! Thanks!
this is what I did for counting yellow cells.
Function CountColor(countRange As Range) As Double
Dim cell As Range
For Each cell In countRange
If cell.Interior.Color = 65535 Then
CountColor = CountColor + 1
End If
Next cell
End Function
Excellent
Thank you . You are super
My pleasure Khalid :)
Excellent again Leila
Glad you like it.
Thanks Leila, you really made it for my requirements.
Thanks Again.
I'm glad to hear that.
Leila thank you! Dream woman right there
Glad you like it :)
I have a range of cells which are conditional formatted with different colors. This doesnt work for that. While summing up in the "Fx" function, the empty cells are treated as " " instead of 0 and hence the overall sum function fails. How can this be handled, please?
Hello, great video. Can you advise how to modify the code if you want the count of color cells (conditionally formatted - upto 5 CF rules ) only and not the sum of cell contents?
No response to this I see
thank you so much!!!!
You're welcome, Yasmine!
Thank you Leila, this is super cool, have a great day
🤗
You're very welcome Katerina. Liebe Gruesse :)
Thank you! That was so easy to follow. When I save the excel document it no longer automatically updates the sum if I change a color. What can I do?
I face that problem too, did you know how to fix it?
I had the same issue, found this on Google and it worked for me. Just added 2 lines to Leila's code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:C")) Is Nothing Then
ActiveSheet.Calculate
ActiveSheet.EnableCalculation = False
ActiveSheet.EnableCalculation = True
End If
End Sub
Hi Leila, I truly appreciate your work, I keep watching your videos as they are extremely informative and I keep learning new techniques in excel. I see this video has some VBA at a medium level. I wanted to learn VBA from a more beginner level and request you to advise me on where to start ..
I have a VBA course that will take you from beginner to advanced. If you are interested you can check it out here: courses.xelplus.com/p/excel-vba-excel-macros
Wow, looks great. That will be my next course after the I finish the excel course from yours.
That's great Stephen. Looking forward to seeing you there.
Excellent mam
Glad you like it.
Fantastic!! Thank you so much!