IndexMatch with multiple values as Match possible ?

[desmond_]

what I am trying to do is;
look up E11:E15 for "s" or "x" and return the value from A11:A15 and sum it up

I hope you can help
thanks in advance

see the example;

..		A	...	E	F	G	H
10		%	...	CW1	CW2	CW3	CW4
11		25%	...	s		s	x
13		75%	...	s	x	x	x
15		10%	...			s
17		Sum	...	=100%	=75%	=110%	=100%

<tbody>
</tbody>

 

[FDibbins]

See if you can adapt this to your data...

	A​	B​	C​	D​	E​
1​	Name	ID	Rank
2​	David	1001​	1​		Steve
3​	Richard	1002​	1​		Mark
4​	Steve	1003​	0​		Taylor
5​	Mark	1004​	0​
6​	Leo	1005​	1​
7​	Michael	1006​	2​
8​	Rafael	1007​	2​
9​	Smith	1008​	2​
10​	Taylor	1009​	0​

E2=IFERROR(INDEX($A:$A,SMALL(IF($C$2:$C$10=0,ROW($C$2:$C$10)),ROWS($A$1:A1))),"")
entered using CTRL SHIFT ENTER, not just enter
then copy down

 

[AhoyNC]

Try:
Copy formula across columns as needed.

Excel Workbook
	A	B	C	D	E	F	G	H
10	%	*	*	*	CW1	CW2	CW3	CW4
11	25%	*	*	*	s	*	s	x
12	75%	*	*	*	s	x	x	x
13	10%	*	*	*	*	*	s	*
14	Sum	*	*	*	100%	75%	110%	100%
Sheet

 

[desmond_]

thanks a lot !

 

[desmond_]

works well

thanks !

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.