The other day I was approached by two of my co-workers who had a problem. They needed to sum the total of their worksheet based on the background of the cell color. Well I said no problem; we can identify the cell color with some visual basic.
With that their eyes glazed over. Too complex for them.
Okay, I only had a few minutes left in the day so I came up with a quick dirty work around.
I first sorted their data by the background color of the cell.
From the Ribbon I chose Data > Sort. I sorted by Column A and my Sort On criteria was Cell Color. I knew they have 5 colors so I added a level of my sort by for each color. This then allowed me to organize their data by background color.
Next I added a helper column next to the data that was highlighted.
I started at the top and Typed Yellow in the first helper cell. I then copied that down so that Yellow appeared next to each highlighted yellow field. I repeated this for Blue, Red, Green, and Orange (there’s no accounting for such an awful color pallet).
Well with my helper column filled in, I just used a simple =SUMIFS calculation based on the value in my helper column.
=SUMIFS(A:A,B:B,"Blue")
=SUMIFS(A:A,B:B,"Yellow")
=SUMIFS(A:A,B:B,"Red")
=SUMIFS(A:A,B:B,"Green")
=SUMIFS(A:A,B:B,"Orange")
The result was a quick work around to get the answers to my co-workers problem.
| A | B | ||
| 1 | Yellow | ||
| 3 | Yellow | ||
| 5 | Yellow | ||
| 7 | Yellow | ||
| 9 | Yellow | ||
| 2 | Blue | ||
| 4 | Blue | ||
| 6 | Blue | ||
| 8 | Blue | ||
| Sumifs Total | Formula | ||
| Blue Total | 20 | =SUMIFS(A:A,B:B,"Blue") | |
| Yellow Total | 25 | =SUMIFS(A:A,B:B,"Yellow") | |
So once again I was reminded that sometimes the most eloquent solution (in this case visual basic) is not necessarily the best solution for my clients.
How would you have handled this situation?
There is an add-in for Excel 2007, 2010, and 2013 called xCELLcolor that provides sum by background color, count by background color, sum by font color, and count by font color formulas without having to use VBA/macros or color columns.
ReplyDeleteFred,
ReplyDeleteThanks for visiting. You are correct.
In fact there are a few add ins that will do this. Unfortunately where I work the IT department has locked down the ability to install programs or add ins.
This comment has been removed by a blog administrator.
ReplyDelete