SUMIF with INDEX MATCH - Only the first match being returned

[danhenshy23]

Hi,

I am having issues with only the first match being returned when using SUMIF combined with IDEX and MATCH.

Here is an example of the formula I am using.

=SUMIF($B$2:$J$2,INDEX($R$2:$R$10,MATCH(K$2,$S$2:$S$10,0)),$B3:$J3)

Example image included below.
For Fred I would want to get 2 for USA and 4 for France, but as the formula is limiting to the first match only I am getting 2 for USA and 1 for France

How do I get around this please?

 

[Domenic]

It looks like it should be . . .

SUM(IF(ISNUMBER(MATCH(Calculation[[#Headers],[Column1]:[Column82.]],IF(Table9[[#All],[Outcome-Reason]:[Outcome-Reason]]=Calculation[[#Headers],[Column85]],Table9[[#All],[Outcome]:[Outcome]]),0)),Calculation[@[Column1]:[Column84]]))

In any case, here's an example...

danhenshy v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Munich	Dortmund	London	Manchester	Liverpool	New York	Washington	Nice	Paris	Germany	England	USA	France				City	Country
3		0	0	0	3	3	2	2	1	3	0	6	2	4				Munich	Germany
4											0	0	0	0				Dortmund	Germany
5											0	0	0	0				London	England
6											0	0	0	0				Manchester	England
7											0	0	0	0				Liverpool	England
8											0	0	0	0				New York	USA
9											0	0	0	0				Washington	USA
10											0	0	0	0				Nice	France
11																		Paris	France
12
13
Sheet1

Cell Formulas
Range	Formula
K3:K10	K3	=SUM(IF(ISNUMBER(MATCH(Table1[[#Headers],[Munich]:[Paris]],IF(Table2[[#All],[Country]:[Country]]=Table1[[#Headers],[Germany]],Table2[[#All],[City]:[City]]),0)),Table1[@[Munich]:[Paris]]))
L3:L10	L3	=SUM(IF(ISNUMBER(MATCH(Table1[[#Headers],[Munich]:[Paris]],IF(Table2[[#All],[Country]:[Country]]=Table1[[#Headers],[England]],Table2[[#All],[City]:[City]]),0)),Table1[@[Munich]:[Paris]]))
M3:M10	M3	=SUM(IF(ISNUMBER(MATCH(Table1[[#Headers],[Munich]:[Paris]],IF(Table2[[#All],[Country]:[Country]]=Table1[[#Headers],[USA]],Table2[[#All],[City]:[City]]),0)),Table1[@[Munich]:[Paris]]))
N3:N10	N3	=SUM(IF(ISNUMBER(MATCH(Table1[[#Headers],[Munich]:[Paris]],IF(Table2[[#All],[Country]:[Country]]=Table1[[#Headers],[France]],Table2[[#All],[City]:[City]]),0)),Table1[@[Munich]:[Paris]]))
Press CTRL+SHIFT+ENTER to enter array formulas.