average with mulitple ciriteria

[Guest]

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.)

 

[Guest]

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

 

[Guest]

that worked a treat - thanks!

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

 

[Guest]

What do the -- signs within the formula represent, or do?

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)