A tricky array sum question into a single cell...

[spup345]

Best to illustrate - Table 1:

ABC	1
DEF	2
GHI	3

Table 2:

XYZ

DEF

LMN

GHI

I would like a formula, in a single cell, that works like: "If the value in Table 1, Column 1 exists anywhere in Table 2, sum the corresponding values in Table 1, Column 2". It would need to be an array I believe.

In the above tables, the cell would return a result of 5 --> DEF and GHI from Table 1, exist in Table 2, so 2 + 3 = 5

I can't figure out what combination (if any) of LOOKUP, or INDEX/MATCH, or SUMIFS or something else can actually do this calc in a single cell...

 

[Cubist]

Which Excel Version are you using?

 

[etaf]

something like
=SUM((SUMIFS(B1:B9,A1:A9,E2:E8)))
where table 2 is in E2:E8

what version of excel are you using

Book15
	A	B	C	D	E	F	G	H
1	ABC	1			Table 2:			5
2	DEF	2			XYZ
3	GHI	3			DEF
4					LMN
5					GHI
6
Sheet1

Cell Formulas
Range	Formula
H1	H1	=SUM((SUMIFS(B1:B9,A1:A9,E2:E8)))

 

[spup345]

Excel 365 I believe. Windows 11 with the latest excel basically…

I will give the above a try and let you know. Thank you!

 

[etaf]

would you update your profile to show 365 version