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?

AVERAGEIFS to return an average between dates

I was asked to find the average sales between two date ranges. Well in earlier posts I described how to use SUMIFS and COUNTIFS to get the sum of values between two dates and to count the number of rows between two dates.

Well another cousin of SUMIFS and COUNTIFS is AVERAGIFS.

AVERAGEIFS allows you to get an average of a range of data base on specific criteria(s) (hence the IFS in AVERAGETFS).

In the above example I have a few columns of Data. I want the average total in Column C that have a date range between 1/5/2011 AND 1/20/2011. The formula is in Cell E2 but I show it to you in Cell D2.

=AVERAGEIFS(C:C,B:B,">1/5/2011",B:B,"<1/20/2011")

If I build the formula with the function Argument dialog box, it would look like the following…

Looking at the data sorted by date, I can see that my calculation is correct since the total of the cells highlighted in yellow is 18,000. When I divide that by 4, I get 4,500.

Some notes on the AVERAGEIFS function….

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Average_range is one or more cells to average, including numbers or names, arrays, or references that contain numbers.

Some samples of the AVERAGEIFS criteria are as follows….

45 – no quotes are required for numbers.

“Invoice” – needs quotes around it since it is text.

“>50” – needs quotes around it since it includes the > sign.

D7 – no quotes requires as it references a cell.

If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.

You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

This formula is not available in Excel 2003 or older.

How have you used AVERAGEIFS to resolve a problem?