sum a range using match and index

[MartinL]

Hi
hope someone can point me in the right direction here. I have gleaned over the posts and may have missed the answer so here goes...

I have a number of worksheets with different bits of data on them but all have a common reference number and always in column A

I am trying to sum a row (say from BN:DF) based on a match and index formula.
these are my attempts.

• =SUM(INDEX(Sales!BN:DF,MATCH(Sheet1!A2,Sales!A:A,0)))
• =SUM(Sales!(match(A2,Sales!A:A,0)BNmatch(A2,Sales!A:A,0))))

It would appear that both are total drivel.

 

[MartinL]

I don't think you need a match and index solution - try this.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Billing	Direct cost	Margin	%	Sales person			Sales person
2	27,600	14000	13,600	49%	Aniket			Aniket	27,600.00
3	312,900	254875	58,025	19%	Caleb			Caleb	1,090,005.00
4	254,100	227382	26,718	11%	Caleb			Pradeep	125,000.00
5	75,000	0	75,000	100%	Pradeep			I****a	585,588.00
6	50,000	0	50,000	100%	Pradeep			Manish	762,976.00
7	273,000	0	273,000	100%	Caleb
8	30,000	21000	9,000	30%	I****a
9	30,000	21000	9,000	30%	I****a
10	535,501	394775	140,726	26%	Manish
11	250,005	158125	91,880	37%	Caleb
12	112,500	7000	105,500	94%	Manish
13	108,975	71845	37,130	34%	Manish
14	6,000	4000	2,000	33%	Manish
15	188,351	126765	61,586	33%	I****a
16	53,815	54131	-316	-1%	I****a
17	283,422	189075	94,347	33%	I****a
Sheet1

 

[clortan]

Hi -- My formula is not summing...please help: need to sum column F after a double match of row 257 & 258 to column C and column D.


=SUM(INDEX('NMA EVENT TRACKER'!$F$4:$F$4000,MATCH('ACCT PLAN'!$E$257:$CJ$257&'ACCT PLAN'!$E$258:$CJ$258,'NMA EVENT TRACKER'!$C$4:$C$4000&'NMA EVENT TRACKER'!$D$4:$D$4000,0)))

 

[FDibbins]

Take a look at using sumifS() instead?

 

[clortan]

Thank you Ford, I will need a little more detail since I could not get this to work.

Header >	Feb W1	Feb W2	Feb W3	Feb W3	Mar W1	Mar W2				DATA	Week	Sum these
Desired Output >	31	16	0	0	0	78				FEB	1	15
										FEB	1	16
										FEB	2	17
										MAR	2	18
										MAR	2	19
										MAR	2	20
										MAR	2	21
Match This	FEB	FEB	FEB	FEB	MAR	MAR	MAR
& Match this	1	2	3	4	1	2	2

<tbody>
</tbody>

 

[Peter_SSs]

Try this, copied across.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Feb W1	Feb W2	Feb W3	Feb W3	Mar W1	Mar W2				DATA	Week	Sum these
2	31	17	0	0	0	78				FEB	1	15
3										FEB	1	16
4										FEB	2	17
5										MAR	2	18
6										MAR	2	19
7										MAR	2	20
8										MAR	2	21
SUMIFS

 

[Peter_SSs]

Actually, this should also do:

=SUMIFS($L2:$L8,$J2:$J8,LEFT(A1,3),$K2:$K8,RIGHT(A1,1))

 

[RyanSc]

Hi there,

As always many thanks in advance to the MrExcel community for support and guidance.
I'm attemtping to create an statement reconcilliation workbook using sum(index(match and am running into some problems.

When using some of the fomulas on this page, the sum calculates the entire column and not the row entries with the match criteria.
See attached workbook for more info.

Any assistance will be much appreciated.
Ryan

=SUM(INDEX($B$5:$B$14,,MATCH(C17,$C$5:$C$14,0)))

SUM(INDEX(MATCH
STATEMENT
-100	ACCOUNTING
-500	ADVERTISING
-50	BANK FEES
-100	RESEARCH
500	SALES
-50	ADVERTISING
750	SALES
-100	ADVERTISING
250	SALES
150	SALES
SUMMARY	Sum/Index/Match		MANUAL CHECK			PASS / FAIL
750	ACCOUNTING		-100	ACCOUNTING
#REF!	ADVERTISING		-650	ADVERTISING
#REF!	BANK FEES		-50	BANK FEES
#REF!	RESEARCH		-100	RESEARCH
#REF!	SALES		1650	SALES
0	EXPENSES		-900	EXPENSES		FAIL
750	INCOME		1650	INCOME		FAIL
750	EBITDA		750	EBITDA

<tbody>
</tbody>

LINK TO FILE: https://www.dropbox.com/s/ymzenayes05nq13/SUM.INDEX.MATCH.xlsx?dl=0

 

[MartinL]

Why not use a sumif instead of the sum match and index
=SUMIF($C$5:$C$14,C17,$B$5:$B$14) copied down

 

[RyanSc]

Why not use a sumif instead of the sum match and index
=SUMIF($C$5:$C$14,C17,$B$5:$B$14) copied down

That would be far too simple.. Joking aside many thanks Martin - sumif working perfectly.