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?
