# averageif + average

1. ## 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  Register To Reply

2. ## Re: averageif + average

Do you need an average per criteria or just an average of each supplier?

If it's for each supplier - see attached  Register To Reply

3. ## Re: averageif + average

Try

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

4. ## Re: averageif + average

I think you need average of supplier.
Formula:  `Please Login or Register  to view this content.`

Supplier range : "A2:A8"
Criteria in "J1"  Register To Reply

5. ## Re: averageif + average

Try this ...

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

Array formula, enter with Ctrl+Shift+Enter.  Register To Reply

6. ## Re: averageif + average

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!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 