Criteria entered in AVERAGEIFS work like an AND function. Only items for which all criteria are met will be included in the average calculation.

Obviously, no item will ever contain "assistent" AND *supervisor" AND *floor", unless there would an Assistent floor supervisor.

Better to create a pivot table (Site Name in the Rows area, Service in the Column area and Final Rate in the Values field, set to calculate Average.

If you have the FILTER function available, you could use:

=IFERROR(AVERAGE(FILTER($D$2:$D$2382,COUNT(SEARCH({"assistant","supervisor","floor"},$C$2:$C$2382)))),0)

If not, it's still achievable with formulas using a SUM(SUMIFS())/SUM(COUNTIFS()) construction.

Thanks Riny & Velouria

@Velouria: the formula works but gives me the average of all 2382 rows, I need to know the average for each of the 188 sites, so need to also lookup site in column A, I'm not sure how to add that to your formula

Thanks

Paul

You can add that in like this:

=IFERROR(AVERAGE(FILTER($D$2:$D$2382,COUNT(SEARCH({"assistant","supervisor","floor"},$C$2:$C$2382))*($a$2:$a$2382="site name"))),0)

Thanks Velouria,

I'm away at the moment, let me try this when I get back, will confirm all OK the,

Regards

Paul

Hi Velouria,

I have attached an updated the attached sheet,

When I add in all other services the old formula (cell C185) returns an average of all rows not the average of the requested rows which contains the words Assistant, Supervisor or Floor.

When I update with the new formula (Cell C184) to look up the average for a site, the new formula returns 0, I presume because it does not know which site to look for, it needs something that first looks at cell B184 then site name in column A

Thanks Paul

Cahnge the formula to:
=FILTER($C$2:$C$181,($A$2:$A$181=B184)*BYROW(SEARCH({"Assistant","Supervisor","Floor"},$B$2:$B$181),LAMBDA(a,COUNT(a))))

and note that there are two you omitted the "Evening Supervisor" (rows 24 and 114) role in your example. That will change the outcome for Site 1 to 13.13 and 11.607 for Site 2.

Thanks Riny