Average using Sumif

Hi All,

Need an help urgently, need to find the average amount for people based on level & rating. Like the way we get it from Pivot, i need to use it using formula.

Have given the data below:

Level Rating Amount
2 1 100
2 1 300
4 4 400
5 3 200
2 1 300
3 2 100
4 2 500
5 3 400
3 4 200

Rating
1 2 3 4 5
Level 2
3
4
5

I understand that using {=SUM((F8:F16=E20)*(G8:G16=F19)*H8:H16)}, i can take the total sum, but need the average amount.

in the example given, for Level 2 with Rating 1, the answer is "233.33"

Is there a formula which would result this?

Thanks in advance

PS: unable to attach the excel.

Try

=AVERAGE(IF((F8:F16=E20)*(G8:G16=F19),H8:H16))

also an array formula

Hi Bob,

Thanks so much..it did worked..but for few cells, it returned DIV/0!. any suggestions. thanks

If you have Excel 2007 or 2010, you could use

=IFERROR(AVERAGE(IF((F8:F16=E20)*(G8:G16=F19),H8:H16)),0)