How to sum multiple columns using index & match

[titamagochang]

Hi...
I hope someone could show me the right direction here...
So, here is my table :

A B C D E F G H I J K L M

ID	STORE	MILK	BREAD	MILK	ORANGE	COFFEE	TEA	BREAD	COFFEE	MILK	BREAD	ORANGE
103279	ABC	2	34	23	45	13	15	16	17	18	15	14
105629	DEF	34	45	24	56	24	13	18	19	22	25	21
105631	GHI	5	67	23	14	15	16	27	14	17	27	15
106236	JKL	67	89	23	12	14	16	26	39	23	14	18
106246	MNO	34	23	13	14	15	16	27	29	32	43	13
105696	PQR	12	14	23	45	32	12	34	28	12	34	29
105633	STU	18	15	16	13	14	56	23	45	23	12	78	19

<tbody>
</tbody>

I'm trying to SUM the rows and store it in the subtotal table below, i put the table in sheet2 :

ID	STORE	MILK	BREAD	ORANGE	COFFEE	TEA
103279	ABC
105629	DEF
105631	GHI
106236	JKL
106246	MNO
105696	PQR
105633	STU

<tbody>
</tbody>

I tried to use SUMIFS, but my columns is about a hundred & my row is about 2 thousand cells...
I very new in using INDEX & MATCH formulas, so this is my attempts :

=SUM(INDEX(Sheet1!A:M8,MATCH(Sheet2!A2,Sheet1!A:A,MATCH(Sheet2!C1,Sheet1!C1:M1,0)),0))

I still use excel 2010.
Thank you

 

[Aladin Akyurek]

A:N of Sheet1 houses the data which must be processed.

Sheet2

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	ID	STORE	MILK	BREAD	ORANGE	COFFEE	TEA
2​	103279	ABC	2	34	45	13	15
3​	105629	DEF	34	45	56	24	13
4​	105631	GHI	5	67	14	15	16
5​	106236	JKL	67	89	12	14	16
6​	106246	MNO	34	23	14	15	16
7​	105696	PQR	12	14	45	32	12
8​	105633	STU	18	15	13	14	56

In C2 enter, copy across, and down:

=SUMIFS(INDEX(Sheet1!$C:$N,0,MATCH(C$1,INDEX(Sheet1!$C:$N,1,0),0)),Sheet1!$A:$A,$A2,Sheet1!$B:$B,$B2)<strike></strike>

 

[Marcelo Branco]

Maybe...

Sheet1

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

1​

ID​

STORE​

MILK​

BREAD​

MILK​

ORANGE​

COFFEE​

TEA​

BREAD​

COFFEE​

MILK​

BREAD​

ORANGE​

2​

103279​

ABC​

2​

34​

23​

45​

13​

15​

16​

17​

18​

15​

14​

3​

105629​

DEF​

34​

45​

24​

56​

24​

13​

18​

19​

22​

25​

21​

4​

105631​

GHI​

5​

67​

23​

14​

15​

16​

27​

14​

17​

27​

15​

5​

106236​

JKL​

67​

89​

23​

12​

14​

16​

26​

39​

23​

14​

18​

6​

106246​

MNO​

34​

23​

13​

14​

15​

16​

27​

29​

32​

43​

13​

7​

105696​

PQR​

12​

14​

23​

45​

32​

12​

34​

28​

12​

34​

29​

8​

105633​

STU​

18​

15​

16​

13​

14​

56​

23​

45​

23​

12​

78​

<tbody>
</tbody>

Sheet2

	A​	B​	C​	D​	E​	F​	G​
1​	ID​	STORE​	MILK​	BREAD​	ORANGE​	COFFEE​	TEA​
2​	103279​	ABC​	43​	65​	59​	30​	15​
3​	105629​	DEF​	80​	88​	77​	43​	13​
4​	105631​	GHI​	45​	121​	29​	29​	16​
5​	106236​	JKL​	113​	129​	30​	53​	16​
6​	106246​	MNO​	79​	93​	27​	44​	16​
7​	105696​	PQR​	47​	82​	74​	60​	12​
8​	105633​	STU​	57​	50​	91​	59​	56​

<tbody>
</tbody>

Formula in C2 copied across and down
=SUMPRODUCT((Sheet1!$C$1:$M$1=C$1)*(Sheet1!$A$2:$A$8=$A2)*Sheet1!$C$2:$M$8)


Hope this helps

M.

 

[pvr928]

Hi...
I hope someone could show me the right direction here...
So, here is my table :

A B C D E F G H I J K L M

ID	STORE	MILK	BREAD	MILK	ORANGE	COFFEE	TEA	BREAD	COFFEE	MILK	BREAD	ORANGE
103279	ABC	2	34	23	45	13	15	16	17	18	15	14
105629	DEF	34	45	24	56	24	13	18	19	22	25	21
105631	GHI	5	67	23	14	15	16	27	14	17	27	15
106236	JKL	67	89	23	12	14	16	26	39	23	14	18
106246	MNO	34	23	13	14	15	16	27	29	32	43	13
105696	PQR	12	14	23	45	32	12	34	28	12	34	29
105633	STU	18	15	16	13	14	56	23	45	23	12	78	19

<tbody>
</tbody>

I'm trying to SUM the rows and store it in the subtotal table below, i put the table in sheet2 :

ID	STORE	MILK	BREAD	ORANGE	COFFEE	TEA
103279	ABC
105629	DEF
105631	GHI
106236	JKL
106246	MNO
105696	PQR
105633	STU

<tbody>
</tbody>

I tried to use SUMIFS, but my columns is about a hundred & my row is about 2 thousand cells...
I very new in using INDEX & MATCH formulas, so this is my attempts :

=SUM(INDEX(Sheet1!A:M8,MATCH(Sheet2!A2,Sheet1!A:A,MATCH(Sheet2!C1,Sheet1!C1:M1,0)),0))

I still use excel 2010.
Thank you

Sounds like an opportunity for a Pivot table - with a twist !

Best to firstly create an Excel table - put your cursor anywhere in the table an ALT + N + T

Then create a Pivot table - ALT + N + V + T

You'll notice that the create Table function has automatically renamed identical columns (ie the second 'MILK' column is 'MILK2' etc). Therefore, when you fill out the Pivot Table the columns you want to sum together don't - because the Pivot Table is treating them as separate columns - which they are.

This is the twist: You need to create calculated fields in the Pivot table to add these similar columns together.

Place the cursor in the pivot table and then Options - Fields, Items, & Sets in the 'Calculations' group, and 'Calculated Field' from the drop down menu.

It should be intuitive from there - just create a name for the calculated field - ie something like MILKTotal and then add the Milk columns together. After you hit ok you'll see 'MILKTotal' appear in the 'Field List' for the PT. Just replace this new Field in the Values section and then remove the individual MILK columns.

Make sure you do a reconciliation to ensure you're capturing each of the individual columns correctly.

It may sound like some work, but it is effective, and sure obviates the need for a lot of performance degrading formulae like SUMIF if you've got lots of data.

A better solution of course is to use PowerPivot !!

Have fun

Cheers

pvr928

 

[Marcelo Branco]

An alternative formula

Sheet2
C2
=SUMIF(Sheet1!$C$1:$M$1,C$1,INDEX(Sheet1!$C$2:$M$8,MATCH($A2,Sheet1!$A$2:$A$8,0),0))
copy across and down

M.

 

[Aladin Akyurek]

A:N of Sheet1 houses the data which must be processed.

[...]

Correction, assuming that Sheet1 lists a store just once...

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	ID	STORE	MILK	BREAD	ORANGE	COFFEE	TEA
2​	103279	ABC	43	65	59	30	15
3​	105629	DEF	80	88	77	43	13
4​	105631	GHI	45	121	29	29	16
5​	106236	JKL	113	129	30	53	16
6​	106246	MNO	79	93	27	44	16
7​	105696	PQR	47	82	74	60	12
8​	105633	STU	57	50	91	59	56

In C2 just enter, copy across, and down:

=SUMIFS(INDEX(Sheet1!$C:$N,MATCH($B2,Sheet1!$B:$B,0),0),INDEX(Sheet1!$C:$N,1,0),C$1)

Otherwise, you need an array-processing formula.

 

[titamagochang]

Oh, thank you so much M, its totally work !!!
I'm really happy about it

 

[titamagochang]

Yessss this is work too,
Thank you so much Aladin