Excluding Blank cells in array formula - #N/A error

[drewber]

Suppose I am wanting to assign points to the completion of my kids chores. As I go through the chores I can rate whether the fully completed chore "Y", Partially completed "P", did not complete "N", or if chore is not assigned to them or not applicable "B"...I'd rather just leave this blank and not enter any letter at all for blank.

I've assigned points to each in letter in A2, B2 section. Y=1 point N=0 points P=0.5 points B has no point value....which is where the #N/A error is coming from.

In researching this type of calculation, the formula that made most sense to me was: =(SUM(INDEX($B$2:$B$5,MATCH($E$7:$E$15,$A$2:$A$5,0)))). If there is a different way to calculate, I'm open to suggestions.

This is actually for a work project where I'll have dozens of elements to rate with Y, N, P or leave blank, then get an a point score or average of points for their tasks. Hoping this all makes sense.

Book1.xlsx
	A	B	C	D	E	F	G
1	Scores:
2	Y	1
3	N	0
4	B
5	P	0.5			Jimmy	Alan	Susan
6				CHORES
7				Completed homework	n	y	y
8				Washed & Dried Dishes	p	p	b
9				Mowed yard & used weedwhacker	y	p	n
10				Cleaned Room	B	y	y
11				Washed Car	y		b
12
13
14
15
16				POINTS	#N/A	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
E16:G16	E16	=(SUM(INDEX($B$2:$B$5,MATCH($E$7:$E$15,$A$2:$A$5,0))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E6:G16	Cell Value	="N"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E6:G6	List	=$A$2:$A$5

 

[JamesCanale]

There is any number of ways to calculate this. xlookups work too. Add the iferror function:

MrExcelPlayground11.xlsx
	A	B	C	D	E	F	G
1	Scores:
2	Y	1
3	N	0
4	B
5	P	0.5			Jimmy	Alan	Susan
6				CHORES
7				Completed homework	n	y	y
8				Washed & Dried Dishes	p	p	b
9				Mowed yard & used weedwhacker	y	p	n
10				Cleaned Room	B	y	y
11				Washed Car	y		b
12
13
14
15
16				POINTS	2.5	3	2
Sheet23

Cell Formulas
Range	Formula
E16:G16	E16	=(SUM(IFERROR(INDEX($B$2:$B$5,MATCH(E7:E15,$A$2:$A$5,0)),0)))

 

[drewber]

Thank you