Average If?

S

Sarah

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!
 
R

Roger Govier

Hi Sarah

Something like
=SUMPRODUCT((rngA=A)*(rngB=B)*RngC)/SUMPRODUCT((rngA=A)*(rngB=B))
 
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)
 
Top