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?

 

[Kaper]

For Fred/Germany (K2 I think) use:

=SUMPRODUCT(($B$1:$J$1=IF($S$1:$S$10=K$1,$R$1:$R$10,""))*$B2:$J2)

and copy right/down

 

[Domenic]

Here's another way...

K3, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=SUM(IF(ISNUMBER(MATCH($B$2:$J$2,IF($S$2:$S$10=K$2,$R$2:$R$10),0)),$B3:$J3))

Hope this helps!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[danhenshy23]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done

 

[danhenshy23]

For Fred/Germany (K2 I think) use:

=SUMPRODUCT(($B$1:$J$1=IF($S$1:$S$10=K$1,$R$1:$R$10,""))*$B2:$J2)

and copy right/down

View attachment 117686

Thanks, this worked perfectly

 

[danhenshy23]

Here's another way...

K3, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=SUM(IF(ISNUMBER(MATCH($B$2:$J$2,IF($S$2:$S$10=K$2,$R$2:$R$10),0)),$B3:$J3))

Hope this helps!

I found that with this the formula doesn't move along the table headers once the array was added. Before CTRL+SHIFT+ENTER the formula does move along with the table headings

 

[Domenic]

In this example I've entered the formula in K3, confirmed it with CTRL_SHIFT+ENTER, and then copied it across. (I didn't copy it down because I don't have the rest of the data.) Does this help?

danhenshy.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				Munich	Germany
3		0	0	0	3	3	2	2	1	3	0	6	2	4				Dortmund	Germany
4																		London	England
5																		Manchester	England
6																		Liverpool	England
7																		New York	USA
8																		Washington	USA
9																		Nice	France
10																		Paris	France
11
12
Sheet1

Cell Formulas
Range	Formula
K3:N3	K3	=SUM(IF(ISNUMBER(MATCH($B$2:$J$2,IF($S$2:$S$10=K$2,$R$2:$R$10),0)),$B3:$J3))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[danhenshy23]

For Fred/Germany (K2 I think) use:

=SUMPRODUCT(($B$1:$J$1=IF($S$1:$S$10=K$1,$R$1:$R$10,""))*$B2:$J2)

and copy right/down

View attachment 117686

With this I get a #VALUE! error from row 85 onwards and it just so happens that the data table I reference in the $S$1:$S$10 and $R$1:$R$10 part of the formula runs until Row 84. Does the #VALUE! error have something to do with how I am referencing the data table?

 

[danhenshy23]

In this example I've entered the formula in K3, confirmed it with CTRL_SHIFT+ENTER, and then copied it across. (I didn't copy it down because I don't have the rest of the data.) Does this help?

danhenshy.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				Munich	Germany
3		0	0	0	3	3	2	2	1	3	0	6	2	4				Dortmund	Germany
4																		London	England
5																		Manchester	England
6																		Liverpool	England
7																		New York	USA
8																		Washington	USA
9																		Nice	France
10																		Paris	France
11
12
Sheet1

Cell Formulas
Range	Formula
K3:N3	K3	=SUM(IF(ISNUMBER(MATCH($B$2:$J$2,IF($S$2:$S$10=K$2,$R$2:$R$10),0)),$B3:$J3))
Press CTRL+SHIFT+ENTER to enter array formulas.

So this is how I have transferred the formula into my actual spreadsheet


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

When dragging across, the formula is sticking to Column85 rather than referencing each of the different headers (86,87,88 etc etc). This only happens if the formula is entered as an Array. If I drag the formula without entering as an Array, the column headings change as expected.