Average Formula

[LadyDi]

Is there a way to find an average of a column based on a criteria? I need something like the SUMIF formula, only I need an average, not a sum.

 

[HaHoBe]

Hi, LadyDi,

if you can´t use the Average-Function you might use SumIf and CountIf instead and build your formula on these functions.

Ciao,
Holger

 

[LadyDi]

That works. I never would have thought to do that. Thank you.

 

[LadyDi]

I just ran into another problem with my formula. I want the formula to eliminate the blank cells. In other words, if there are three rows for one order, and one of the rows does not contain a number, I want the average to equal the sum of the cells divided by two, not three. How can I do that? Below is the formula that I have now.

=SUM(SUMIF(Sheet1!$C$2:$C$850, Sheet2!A777, Sheet1!$G$2:$G$850)/ COUNTIF(Sheet1!$C$2:$C$850, Sheet2!A777))

 

[HaHoBe]

Hi, LadyDi,

I´m not very good at guessing but maybe SUMPRODUCT could do a better job. And there are plenty of functions still available which might suit better.

Ciao,
Holger

 

[Brianwarnock]

As you have a criteria in the Countif why will it count blank or empty cells?

Brian