MrExcel Publishing
Your One Stop for Excel Tips & Solutions

IF(AND(AVERAGE)) - Is it Possible???

Posted by Eric on September 27, 2001 2:59 PM

I've been trying to figure this out for a while now.

Column A Column B Column C
A 2 84.6
B 2 85.5
B 3 93.1
A 2 73.4
A 3 73.8
B 3 96.4
A 2 87.0

Now this is my problem, I want the AVERAGE from Column C, IF Column A is "A", AND Column B is "2". Is this possible? Any ideas would be greatly appreciated, I've exhausted all of my own.


Posted by IML on September 27, 2001 3:07 PM

Try the following array (hit enter while control and shift are depressed:

Posted by Eric on September 27, 2001 3:32 PM

Thanks, but I think my example was unclear. Columns A & B are not numerical values, the value of Column C comes from a different formula. Let me give a better example:


Looking for the AVERAGE of Column C, IF Column A is "Blue" AND Column B is "Small"? I think this is a better example. Thanks again for any replies.

Posted by Aladin Akyurek on September 27, 2001 9:53 PM

Three Alternatives

Eric --

The array formula IML suggests will simply do what you want: modify it to fit your new example as


Don't forget using CONTROL+SHIFT+ENTER to enter it.

You can also use SUMPRODUCT version suggested at


or DAVERAGE suggested at