Average If

[Alex]

is there a function that will does the same thing as sumif
and countif except it averages the total amounts

 

[Charles Maxson]

Alex,

You can use a combination of the AVERAGE function and the IF function in an
array formula ({Ctrl} + {Shift} +{Enter}) to make this work.

Here's an example:

=AVERAGE(IF(A1:A100>100,A1:A100,FALSE))

 

[Jason Morin]

No, but you can create a formula to mimic SUMIF and
COUNTIF. For example, to average A1:A100 if B1:B100 is
greater than 4:

=AVERAGE(IF(B1:B100>4,A1:A100))

This is an array formula, and you must press
ctrl/shift/enter for it to work. Excel will place {}
around the formula.

HTH
Jason
Atlanta, GA

 

[Guest]

Try:

=SUMIF(B:B,"x",A:A)/COUNTIF(B:B,"x")