**Sheet1**, A1:C6, houses the data, the headers included.

**Sheet2**, A2, houses a criterion value like supplier 1.

In B2 of Sheet2 control+shift+enter, not just enter...

=AVERAGE(IF(ISNUMBER(AVERAGEIFS(Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6,A2,Sheet1!$B$2:$B$6,IF(FREQUENCY(IF(Sheet1!$A$2:$A$6=A2,MATCH(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6,0)),ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),Sheet1!$B$2:$B$6))),AVERAGEIFS(Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6,A2,Sheet1!$B$2:$B$6,IF(FREQUENCY(IF(Sheet1!$A$2:$A$6=A2,MATCH(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6,0)),ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),Sheet1!$B$2:$B$6))))

If you would want to install the V() function, we can have:

=AVERAGE(IF(ISNUMBER(V(AVERAGEIFS(Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6,A2,Sheet1!$B$2:$B$6,IF(FREQUENCY(IF(Sheet1!$A$2:$A$6=A2,MATCH(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6,0)),ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),Sheet1!$B$2:$B$6)))),V()))

VBA of V() is...

Public Function V(Optional vrnt As Variant) As Variant

'

' Stephen Dunn

' 2002-09-12

'

Static vrntV As Variant

If Not IsMissing(vrnt) Then vrntV = vrnt

V = vrntV

End Function