Fillable table with sum from a range

[DavidFG]

Hello,

I am trying to acquire the sum in a cell (I17) from a range of cells from a table (I8:I16 in Sheet1) pulled in from another worksheet (Sheet4 same spreadsheet) but it doesn't seem to work. Each cell has text from the table that represents a numerical value. I have included the screenshots to better explain and not sure if this is possible as I cannot seem to figure it out after trying different formulas.

Is there a way to do this? I appreciate your help.


Thank you for your time.

 

[kweaver]

Welcome to Mr. Excel.
It would help if you used XL2BB to show your data and expected results. Pasted images are very difficult to read and understand.

If 13 the answer you're looking for in your example?

Book1
	I	J	K	L
7			Sheet4
8	Pass		Pass	2
9	NA		NA	2
10	NI		NI	1
11	Fail		Fail	0
12	Pass
13	Pass
14	Pass
15	NA
16	Fail
17	13
Sheet1

Cell Formulas
Range	Formula
I17	I17	=SUM(XLOOKUP(I8:I16,K8:K11,L8:L11))

 

[DavidFG]

=SUM(XLOOKUP(I8:I16,K8:K11,L8:L11))

Thank you kweaver! That helped me figure it out. I used =SUM(XLOOKUP(I8:I16,Sheet4!A2:A5,Sheet4!B2:B5))