averageif + average

Hey there,

so I got an Excel Problem.
I have a list of suppliers, which are evaluated (1 = Excellent, 5 = Bad) by 7 different criteria. The problem here is, that there are loads of people, who will evaluate the same supplier, so I will receive different evaluations for one supplier. Now, I want to calculate the average evaluation for each supplier.
I've tried averageif, but it won't calculate the average of all seven criterias in the columns and add it up with each line, but only the average of criteria1 between the lines.

excelprob.png

Is there a possibility to receive my result with only one formula?
It's pretty essential to get it in one formula, since my data will update regularly and I need the formula to keep up automatically.

So what I am looking for is something that will work like this:
Artweger = ((2 + 3 + 2 + 3 + 3 + 3 + 3)/7 + (1 + 1+ 2 + 2+ 2+ 1 + 1)/7)/2

Hope it's somewhat understandable!

All the best,
Jasmin

Try

=SUMPRODUCT(($B$2:$H$100))/(SUMPRODUCT(--($A$2:$A$100="Artweger")*7))

Try this ...

=AVERAGE(IF($A$2:$A$7=A10,$B$2:$H$7))

Array formula, enter with Ctrl+Shift+Enter.

Thank you so much guys!
Thanks to your help, I found something that works fine for me!

=SUMPRODUCT((A:A="Artweger)*B:H)/7/COUNTIF(A:A;"Artweger")

I had to exclude the first row (non-numerical values), so I adapted the first part of the formula furthermore: =SUMPRODUCT((A2:A2000="Artweger")*B2:H2000)/7/COUNTIF(A:A;"Artweger")

Thank you guys so much,
have a pleasant day!