Average If?

[Guest]

Hi there,
I need to average the contents of 1 column of data if they meet the criteria
of 2 other columns of data. So basically like SumIf but Average If. Is this
possible? Am familiar with SumProduct which would be my first stop for 3
range relationships, but can't work out how to build an average into it.

Thanks!

 

[Roger Govier]

Hi Sarah

Something like
=SUMPRODUCT((rngA=A)*(rngB=B)*RngC)/SUMPRODUCT((rngA=A)*(rngB=B))

 

[Bob Phillips]

=AVERAGE(IF((rng>number1)*(rng<number2),rng))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Excellent - thanks!

Hi Sarah

Something like
=SUMPRODUCT((rngA=A)*(rngB=B)*RngC)/SUMPRODUCT((rngA=A)*(rngB=B))