average with mulitple ciriteria

E

ellebelle

I want to get the average with two criteria. if the cells in column Z = B11
then I want the average value from column CA (BUT I only want the average of
cells greater than zero.)
 
B

bpeltzer

You could use sumproduct to add the appropriate cells and to the count the
appropriate cells, then just divide the two:
=sumproduct(--(z1:z5000=b11),--(ca1:ca5000>0),(ca1:ca5000)) /
sumproduct(--(z1:z5000=b11),--(ca1:ca5000>0))
--Bruce
 
E

ellebelle

that worked a treat - thanks!

bpeltzer said:
You could use sumproduct to add the appropriate cells and to the count the
appropriate cells, then just divide the two:
=sumproduct(--(z1:z5000=b11),--(ca1:ca5000>0),(ca1:ca5000)) /
sumproduct(--(z1:z5000=b11),--(ca1:ca5000>0))
--Bruce
Click to expand...
 
Top