I saw a great post recently from a fellow Excel geek about summing Excel cells by color. So, for example, it'll add up all the cells that have a blue background. See his solution here: https://www.linkedin.com/posts/hazemhassandrexcel_color-excel-hazemabrhassan-activity-7171171424525565952-p9EL?utm_source=share&utm_medium=member_desktop
I thought it was a cool technique, so I looked at the example and tried to recreate it. The author had completed this without using any VBA code. Instead, it required creating a complex formula, embedding that within a named range, and then referencing that name in a formula.
Using his example, I spent a few minutes setting up the formula, only to discover that it wouldn't work for me. I spent several more minutes troubleshooting, but still couldn't make it work. I double- and triple-checked my work, making sure I hadn't mistyped anything or misplaced a comma, but couldn't find the problem.
So, I turned to Google. It took me about 10 minutes of googling to discover the problem. The formula used a very old Excel function that was blocked by my security settings. I changed those settings and was able to get the formula to work.
But a few minutes later, when I switched from one laptop to another, the formula once again failed. I checked all of the security settings on that laptop, but nothing worked. I spent another 30 minutes googling the issues and tried at least 4 or 5 possible fixes. No luck.
At this point, I had well over an hour into this project. I took a step back and thought about it for a minute.
I came to three realizations.
Since I did not have a client asking for this particular solution, how much of my time was it worth?
If this function is so difficult to get working on different computers, I'd be unlikely to trust it in a client's solution.
I could probably use VBA to solve the problem in a lot less time.
Based on numbers 1 and 2 above, I should have given up at this point. But I hate to give up, so instead, I decided to prove point #3, and solve the problem with VBA. I created a slight variation which counts the rows by color, rather than summing them.
Sure enough, it only took me about 15 minutes to code and test the VBA solution. As you can see here, I've got an inventory list, and I've highlighted products that are on sale this month and next month. My formula in cells H3 and H4 reference the VBA function, showing me a count of products on sale each month.
The formula is cells H3 and H4 is very simple:
The lesson here is: sometimes the "hard" way is actually the "easy" way.
I talk to a lot of people who consider VBA to be the hard way, no matter the problem. But in this case, it was, by far, the easier solution. Of course, I've got a lot of VBA experience, so it was easier for me than it would be for most. But still, given the complexity of the formulas, plus the skill level required for the research and troubleshooting, I suspect it would be quicker and easier for a lot of people to learn the tiny bit of VBA that would be necessary to implement this solution.
In this case, implementing the solutions with the "hard" VBA method was far easier than the "easy" codeless way.
The best part of my VBA solution is that it is easily transferred to other projects. The code is encapsulated in a module which can be inserted into any Excel file, giving you quick and easy access to the function in your future work. Who doesn't love repurposing their prior work?
If you've got a project that would benefit from this color-sensitive counting or summing, reach out to me. Now that I've got the functions defined, it'll be quick and easy to add to your project.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2024. All rights reserved.