Average with more than one IF statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have used the formula from Bob Philips (10th Sept 06) in my worksheet:
=AVERAGE(IF(ISNUMBER('eligible 2% - 1jan'!U3:U501),'eligible 2% -
1jan'!U3:U501)).
I have to get the average also per department, so I have to check whether it
is the correct department before it calculates the average. Not all info is
filled in the U column.
For the summation I have used the following: =SUMIF('eligible 2% -
1jan'!$A$3:$A$501,"mt*",'eligible 2% - 1jan'!$AB$3:$AB$501). ex* being a
department starting with mt. But for average I have to omit the zero values.
Any help is appreciated. Thanks, Lupe
 
Hi,
I have tried this formula, it works, but it does not work with the mt*. I
need to average amounts for various departments (various abbreviations) that
we need to group together.
=AVERAGE(IF((ISNUMBER(D1:D100))*(E1:E100="mt"),D1:D100))
Lupe
 
Thanks Dave.
The formula works. Is this the argument (LEFT) I should use also in my other
formulas of SUMIF's? I assume it is better than the *.
Regards, Lupe
 
=sumif() can support wildcards (like mt*).

You don't need/want to change it. You'll see the other problem when/if you try
changing the =sumif() formula.
 
Dave,
Thanks again.
Lupe

Dave Peterson said:
=sumif() can support wildcards (like mt*).

You don't need/want to change it. You'll see the other problem when/if you try
changing the =sumif() formula.
Click to expand...
 
Back
Top