### Sum based on cell background color

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?