SUM INDEX MATCH Help.

[tandkb]

Hi I am currently using this formula.

=IFERROR(INDEX(Transfers!$J$5:$J$999999,MATCH(A68&$A$61,Transfers!$F$5:$F$999999&Transfers!$H$5:$H$999999,0),MATCH($A$3,Transfers!$E$5:$E$999999,0)),0)

It works great except it does not sum multiple matches it only find the first match in the table. I need it to SUM all the matches in the table.

Thanks so much for your help in advance!

 

[kweaver]

Without trying to reproduce your roughly million rows, I would suggest you consider SUMPRODUCT as an option.
Have you tried that?

Or post a smaller version of your file with examples.

 

[tandkb]

Without trying to reproduce your roughly million rows, I would suggest you consider SUMPRODUCT as an option.
Have you tried that?

Or post a smaller version of your file with examples.

The number of rows dont matter. I just need to know where to put the SUMProduct in the formula. Everywhere that is 9999999 you can replace with just a 9.

Thanks so much.

 

[DanteAmor]

Try this

=SUMPRODUCT((A68&$A$61=Transfers!$F$5:$F$9&Transfers!$H$5:$H$9)*($A$3=Transfers!$E$5:$E$9)*(Transfers!$J$5:$J$9))

 

[kweaver]

DanteAmor has written the sumproduct.

His example sums the J column when the concatenation of the two A-column values match the Transfers in the F column and the A3 value matches the E column in Transfers.

 

[tandkb]

I still need it to INDEX MATCH because there are 3 criteria that has to match in order for it to SUM the correct numbers. Can i do SUM with INDEX MATCH?

 

[DanteAmor]

This is my example and it works.


You can set your example, say what you want to add and what the result is.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2												21
3	a3
4
5					a3	a68		a61		5
6					a4	a68		a61		6
7					a3	a68		a61		7
8					a4	a68		a61		8
9					a3	a68		a61		9
60
61	a61
62
67
68	a68
69
Transfers

 

[tandkb]

DanteAmor has written the sumproduct.

His example sums the J column when the concatenation of the two A-column values match the Transfers in the F column and the A3 value matches the E column in Transfers.

Ok so the concatenation of the two A-column values need to match the Transfers F and H Columns and A3 matches E Column in Transfers.

How would that work?? Thanks again!

 

[tandkb]

I am sorry. I am an idiot. having the 9 in there did not give me all my data and when I corrected it. It worked perfect. Thanks so much. The sum product is a much easier forumula than the index match. Is there a way to do the index match I am using with out getting the sum easier than using an index match?

That did not make a whole lot of sense. I am using the Index Match for a different calculation and it is very resource intensive. Can I get the same result not using that and make my sheet more responsive?

Thanks!

 

[DanteAmor]

To improve sheet performance, you must change 999999 to a smaller number, perhaps a slack of 10 additional rows over the last cell with data.
Having several array formulas does not improve sheet performance.
If the sheet is very slow, then you could change some formulas for macros.