sum index match formula

[excelprk]

Per attached, I am trying to find sum index match formula for columns N and O given store ID numbers with glyphs in between in column M. Please assume column M is a dropdown hence why we have extended index range.

 

[AhoyNC]

Welcome to the forum.
Try the SUMIFS function

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	store id	store name	# of Employees	# of Customers			store id	no store namOther stores nearby (<lmin) Other stores nearby (<5min)				Store Name Other stores nearby (<lmin)		# of Employees	# of Customers
2	2	Red	2	40			2	Red	4|6	4|6|7		Blue	5| 6	11	35
3	4	Blue	2	20			4	Blue	5|6	5|6|7		Orange	2|4|7	5	30
4	5	Orange	5	30			5	Orange	2	2|7
5	6	Green	7	25			6	Green	2|4|7	2|4|7
6	7	Blue	9	15			7	Blue	4|6	4|6|7
Sheet2

Cell Formulas
Range	Formula
N2:O3	N2	=SUMIFS(C2:C6,$B2:$B6,$L2)

 

[excelprk]

Thank you! To clarify, sum for columns N and O should be based on the collective sum of store IDs in column M. Also to add another layer to that, would you recommend another formula to get unique sum without double counting store IDs in columns N and O assuming there are same store ID overlaps in blue and orange

 

[AhoyNC]

Something like this? This formula will not work in Excel 2019, not sure about Excel 2021.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	store id	store name	# of Employees	# of Customers			store id	no store namOther stores nearby (<lmin) Other stores nearby (<5min)				Store Name Other stores nearby (<lmin)		# of Employees	# of Customers
2	2	Red	2	40			2	Red	4|6	4|6|7		Blue	5| 6	12	55
3	4	Blue	2	20			4	Blue	5|6	5|6|7		Orange	2|4|7	13	75
4	5	Orange	5	30			5	Orange	2	2|7
5	6	Green	7	25			6	Green	2|4|7	2|4|7
6	7	Blue	9	15			7	Blue	4|6	4|6|7
Sheet2

Cell Formulas
Range	Formula
N2:O3	N2	=SUM(SUMIFS(C$2:C$6,$A$2:$A$6,TEXTSPLIT($M2,"|")+0))

 

[AhoyNC]

This should only count unique stores in column M (see cell M2 in the example).
This formula won't work in Excel 2019.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	store id	store name	# of Employees	# of Customers			store id	store name	Other stores nearby (<lmin)	Other stores nearby (<5min)		Store Name	Other stores nearby (<lmin)	# of Employees	# of Customers
2	2	Red	2	40			2	Red	4|6	4|6|7		Blue	5| 6|5	12	55
3	4	Blue	2	20			4	Blue	5|6	5|6|7		Orange	2|4|7	13	75
4	5	Orange	5	30			5	Orange	2	2|7
5	6	Green	7	25			6	Green	2|4|7	2|4|7
6	7	Blue	9	15			7	Blue	4|6	4|6|7
Sheet1

Cell Formulas
Range	Formula
N2:O3	N2	=SUM(SUMIFS(C$2:C$6,$A$2:$A$6,UNIQUE(TEXTSPLIT($M2,"|"),1)))