How to use Calculate average with multiple criteria and ignore text

[alantse2010]

Hello all, i want to calculate the average refer the contractor name and ignore the text "NA".
I know that it needs to use averageifs to calculate but it shows the #DIV/0! error if the data contain text or no text
Would you please tell me how to fix it?
I had wrote down my formula and the result in below
Thank you
Sheet1:

Contractor	Meet or exceed customer’s requirements2	Manpower Resources of subcontractor2	Cooperative of Subcontractor’s staff2	Adequate safety measures2
WY	4​	4	5​	4
AT	4​	NA	4​	NA
TS-SP	4​	3	5​	4
TS-SP	5​	3	4​	NA
AT	3​	3	3​	3
TS-SP	2​	2	2​	2
WY	4​	4	4​	4
TS-WS	3​	2	4​	4
WL	5​	5	5​	5
YF	1​	1	1​	1

Sheet2:
Result:

Contractor	Average mark of adequate safety measures 2
AT	=AVERAGEIFS(Sheet1[Adequate safety measures2],Sheet1[Contractor],"AT", Sheet1[Adequate safety measures2],"<>NA")
TS-SP	=AVERAGEIFS(Sheet1[Adequate safety measures2],Sheet1[Contractor],"TS-SP", Sheet1[Adequate safety measures2],"<>NA")
WY
TS-WS
YF
WL

 

[Snakehips]

@alantse2010 Might this help?
It is an Array formula.

MRXLMAY21 (version 1).xlsb
	A	B	C	D	E	F	G
1	Product Code	Contractor	Meet or exceed customer’s requirements2	Manpower Resources of subcontractor2	Cooperative of Subcontractor’s staff2	Adequate safety measures2	Average Ignoring Text Other Than Number As Text
2	ABC123	WY	4	4	5	4	4.25
3		AT	4	4	4	NA	4
4		TS-SP	4	3	5	4	4
5		TS-SP	5	3	4	NA	4
6		AT	3	3	3	3	3
7		TS-SP	2	2	2	2	2
8		WY	4	4	4	4	4
9		TS-WS	3	2	4	4	3.25
10		WL	5	5	5	5	5
11		YF	1	1	1	1	1
Sheet3

Cell Formulas
Range	Formula
G2:G11	G2	=SUMPRODUCT((IFERROR(C2:F2*1,0)))/SUMPRODUCT(ISNUMBER(C2:F2*1)*1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Toadstool]

Hi Alantse2010,

Your example seems to work OK (although I'm using an earlier Excel version). If all the entries for a Contractor are NA then yes, you will get a #DIV/0 error but you can trap that with an IFERROR.

Book1
	A	B	C	D	E
1	Contractor	Meet or exceed customer’s requirements2	Manpower Resources of subcontractor2	Cooperative of Subcontractor’s staff2	Adequate safety measures2
2	WY	4	4	5	4
3	AT	4	NA	4	NA
4	TS-SP	4	3	5	4
5	TS-SP	5	3	4	NA
6	AT	3	3	3	3
7	TS-SP	2	2	2	2
8	WY	4	4	4	4
9	TS-WS	3	2	4	4
10	WL	5	5	5	5
11	YF	1	1	1	1
Sheet1

Book1
	A	B
1	Contractor	Average mark of adequate safety measures 2
2	AT	3
3	TS-SP	3
4	TS-WS	4
5	WL	5
6	WY	4
7	YF	1
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(AVERAGEIFS(Adequate_safety_measures2,Contractor,A2,Adequate_safety_measures2,"<>NA"),0)

Named Ranges
Name	Refers To	Cells
Adequate_safety_measures2	=Sheet1!$E$2:$E$11	B2:B7
Contractor	=Sheet1!$A$2:$A$11	B2:B7

 

[Snakehips]

Sorry folk but I just realised I totally lost the plot on this one!

 

[alantse2010]

@Snakehips thank you for your reply, I tried your formula, it is successful to calculate the average and pass the text"NA" but the directional should be calculated from F2:F11 and refer to the contractor Name
@Toadstool Thank you for your reply, I tried your formula, I think your formula is right but I don't know why it is still showing "#DIV/0!", so I copy the data to another sheet and clear NA and calculate again, it works, thank you

 

[Toadstool]

@Snakehips thank you for your reply, I tried your formula, it is successful to calculate the average and pass the text"NA" but the directional should be calculated from F2:F11 and refer to the contractor Name
@Toadstool Thank you for your reply, I tried your formula, I think your formula is right but I don't know why it is still showing "#DIV/0!", so I copy the data to another sheet and clear NA and calculate again, it works, thank you

You're welcome!