MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Average If


Posted by David on November 30, 2001 7:59 PM

I'd like to average the numbers in column B if certain criteria is met in the adjacent cell in column A. For instance, assume A1:A6 is red, blue, red, blue, red, blue and B1:B6 is 2,4,6,3,5,7. What formula could I put in a cell in column C that would average only the numbers in B that have "blue" next to them in A? Is it possible?


Posted by Johnny on November 30, 2001 8:20 PM

Got one solution to your problem if you can use an extra column (that you can hide):

In the hidden column (example D1)
=If(A1="blue";B1;"") That will give you the number next to blue.
In C, just put in =Average(D1:D6) and you have your average of the numbers next to blue.

Probably many other solutions to thisÂ…this is the one I came up with.

Johnny

Posted by Aladin Akyurek on November 30, 2001 10:12 PM

David --

=SUMIF(A1:A6,"blue",B1:B6)/MAX(1,COUNTIF(A1:A6,"blue")

Aladin

Posted by David on December 01, 2001 8:51 AM

Thanks! - that does the trick!!!