How to sum multiple columns using index & match

T

titamagochang

New Member
Joined
Mar 3, 2016
Messages
5
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
[TABLE="width: 896"]
<tbody>[TR]
[TD="class: xl66, width: 64"]ID[/TD]
[TD="class: xl66, width: 64"]STORE[/TD]
[TD="class: xl66, width: 64"]MILK[/TD]
[TD="class: xl66, width: 64"]BREAD[/TD]
[TD="class: xl66, width: 64"]MILK[/TD]
[TD="class: xl66, width: 64"]ORANGE[/TD]
[TD="class: xl66, width: 64"]COFFEE[/TD]
[TD="class: xl66, width: 64"]TEA[/TD]
[TD="class: xl66, width: 64"]BREAD[/TD]
[TD="class: xl66, width: 64"]COFFEE[/TD]
[TD="class: xl66, width: 64"]MILK[/TD]
[TD="class: xl66, width: 64"]BREAD[/TD]
[TD="class: xl66, width: 64"]ORANGE[/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]103279[/TD]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105629[/TD]
[TD="class: xl66"]DEF[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]24[/TD]
[TD="class: xl66"]56[/TD]
[TD="class: xl66"]24[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]19[/TD]
[TD="class: xl66"]22[/TD]
[TD="class: xl66"]25[/TD]
[TD="class: xl66"]21[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105631[/TD]
[TD="class: xl66"]GHI[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]67[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]106236[/TD]
[TD="class: xl66"]JKL[/TD]
[TD="class: xl66"]67[/TD]
[TD="class: xl66"]89[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]39[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]106246[/TD]
[TD="class: xl66"]MNO[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl66"]32[/TD]
[TD="class: xl66"]43[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105696[/TD]
[TD="class: xl66"]PQR[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]32[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]28[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105633[/TD]
[TD="class: xl66"]STU[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]56[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]78[/TD]
[TD="class: xl66, align: right"]19[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to SUM the rows and store it in the subtotal table below, i put the table in sheet2 :
[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl66, width: 64"]ID[/TD]
[TD="class: xl66, width: 64"]STORE[/TD]
[TD="class: xl66, width: 64"]MILK[/TD]
[TD="class: xl66, width: 64"]BREAD[/TD]
[TD="class: xl66, width: 64"]ORANGE[/TD]
[TD="class: xl66, width: 64"]COFFEE[/TD]
[TD="class: xl66, width: 64"]TEA[/TD]
[/TR]
[TR]
[TD="class: xl65"]103279[/TD]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105629[/TD]
[TD="class: xl66"]DEF[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105631[/TD]
[TD="class: xl66"]GHI[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]106236[/TD]
[TD="class: xl66"]JKL[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]106246[/TD]
[TD="class: xl66"]MNO[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105696[/TD]
[TD="class: xl66"]PQR[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105633[/TD]
[TD="class: xl66"]STU[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

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 :)
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
A:N of Sheet1 houses the data which must be processed.

Sheet2

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr]
[tr][td]
1​
[/td][td] ID[/td][td] STORE[/td][td] MILK[/td][td] BREAD[/td][td] ORANGE[/td][td] COFFEE[/td][td] TEA[/td][/tr]


[tr][td]
2​
[/td][td] 103279[/td][td] ABC[/td][td] 2[/td][td] 34[/td][td] 45[/td][td] 13[/td][td] 15[/td][/tr]


[tr][td]
3​
[/td][td] 105629[/td][td] DEF[/td][td] 34[/td][td] 45[/td][td] 56[/td][td] 24[/td][td] 13[/td][/tr]


[tr][td]
4​
[/td][td] 105631[/td][td] GHI[/td][td] 5[/td][td] 67[/td][td] 14[/td][td] 15[/td][td] 16[/td][/tr]


[tr][td]
5​
[/td][td] 106236[/td][td] JKL[/td][td] 67[/td][td] 89[/td][td] 12[/td][td] 14[/td][td] 16[/td][/tr]


[tr][td]
6​
[/td][td] 106246[/td][td] MNO[/td][td] 34[/td][td] 23[/td][td] 14[/td][td] 15[/td][td] 16[/td][/tr]


[tr][td]
7​
[/td][td] 105696[/td][td] PQR[/td][td] 12[/td][td] 14[/td][td] 45[/td][td] 32[/td][td] 12[/td][/tr]


[tr][td]
8​
[/td][td] 105633[/td][td] STU[/td][td] 18[/td][td] 15[/td][td] 13[/td][td] 14[/td][td] 56[/td][/tr]
[/table]

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>
 
Upvote 0
Maybe...

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[TD="bgcolor: #DCE6F1"]
J​
[/TD]
[TD="bgcolor: #DCE6F1"]
K​
[/TD]
[TD="bgcolor: #DCE6F1"]
L​
[/TD]
[TD="bgcolor: #DCE6F1"]
M​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
ID​
[/TD]
[TD]
STORE​
[/TD]
[TD]
MILK​
[/TD]
[TD]
BREAD​
[/TD]
[TD]
MILK​
[/TD]
[TD]
ORANGE​
[/TD]
[TD]
COFFEE​
[/TD]
[TD]
TEA​
[/TD]
[TD]
BREAD​
[/TD]
[TD]
COFFEE​
[/TD]
[TD]
MILK​
[/TD]
[TD]
BREAD​
[/TD]
[TD]
ORANGE​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
103279​
[/TD]
[TD]
ABC​
[/TD]
[TD]
2​
[/TD]
[TD]
34​
[/TD]
[TD]
23​
[/TD]
[TD]
45​
[/TD]
[TD]
13​
[/TD]
[TD]
15​
[/TD]
[TD]
16​
[/TD]
[TD]
17​
[/TD]
[TD]
18​
[/TD]
[TD]
15​
[/TD]
[TD]
14​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
105629​
[/TD]
[TD]
DEF​
[/TD]
[TD]
34​
[/TD]
[TD]
45​
[/TD]
[TD]
24​
[/TD]
[TD]
56​
[/TD]
[TD]
24​
[/TD]
[TD]
13​
[/TD]
[TD]
18​
[/TD]
[TD]
19​
[/TD]
[TD]
22​
[/TD]
[TD]
25​
[/TD]
[TD]
21​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
105631​
[/TD]
[TD]
GHI​
[/TD]
[TD]
5​
[/TD]
[TD]
67​
[/TD]
[TD]
23​
[/TD]
[TD]
14​
[/TD]
[TD]
15​
[/TD]
[TD]
16​
[/TD]
[TD]
27​
[/TD]
[TD]
14​
[/TD]
[TD]
17​
[/TD]
[TD]
27​
[/TD]
[TD]
15​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
106236​
[/TD]
[TD]
JKL​
[/TD]
[TD]
67​
[/TD]
[TD]
89​
[/TD]
[TD]
23​
[/TD]
[TD]
12​
[/TD]
[TD]
14​
[/TD]
[TD]
16​
[/TD]
[TD]
26​
[/TD]
[TD]
39​
[/TD]
[TD]
23​
[/TD]
[TD]
14​
[/TD]
[TD]
18​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
106246​
[/TD]
[TD]
MNO​
[/TD]
[TD]
34​
[/TD]
[TD]
23​
[/TD]
[TD]
13​
[/TD]
[TD]
14​
[/TD]
[TD]
15​
[/TD]
[TD]
16​
[/TD]
[TD]
27​
[/TD]
[TD]
29​
[/TD]
[TD]
32​
[/TD]
[TD]
43​
[/TD]
[TD]
13​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
105696​
[/TD]
[TD]
PQR​
[/TD]
[TD]
12​
[/TD]
[TD]
14​
[/TD]
[TD]
23​
[/TD]
[TD]
45​
[/TD]
[TD]
32​
[/TD]
[TD]
12​
[/TD]
[TD]
34​
[/TD]
[TD]
28​
[/TD]
[TD]
12​
[/TD]
[TD]
34​
[/TD]
[TD]
29​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
105633​
[/TD]
[TD]
STU​
[/TD]
[TD]
18​
[/TD]
[TD]
15​
[/TD]
[TD]
16​
[/TD]
[TD]
13​
[/TD]
[TD]
14​
[/TD]
[TD]
56​
[/TD]
[TD]
23​
[/TD]
[TD]
45​
[/TD]
[TD]
23​
[/TD]
[TD]
12​
[/TD]
[TD]
78​
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
ID​
[/TD]
[TD]
STORE​
[/TD]
[TD]
MILK​
[/TD]
[TD]
BREAD​
[/TD]
[TD]
ORANGE​
[/TD]
[TD]
COFFEE​
[/TD]
[TD]
TEA​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
103279​
[/TD]
[TD]
ABC​
[/TD]
[TD]
43​
[/TD]
[TD]
65​
[/TD]
[TD]
59​
[/TD]
[TD]
30​
[/TD]
[TD]
15​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
105629​
[/TD]
[TD]
DEF​
[/TD]
[TD]
80​
[/TD]
[TD]
88​
[/TD]
[TD]
77​
[/TD]
[TD]
43​
[/TD]
[TD]
13​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
105631​
[/TD]
[TD]
GHI​
[/TD]
[TD]
45​
[/TD]
[TD]
121​
[/TD]
[TD]
29​
[/TD]
[TD]
29​
[/TD]
[TD]
16​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
106236​
[/TD]
[TD]
JKL​
[/TD]
[TD]
113​
[/TD]
[TD]
129​
[/TD]
[TD]
30​
[/TD]
[TD]
53​
[/TD]
[TD]
16​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
106246​
[/TD]
[TD]
MNO​
[/TD]
[TD]
79​
[/TD]
[TD]
93​
[/TD]
[TD]
27​
[/TD]
[TD]
44​
[/TD]
[TD]
16​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
105696​
[/TD]
[TD]
PQR​
[/TD]
[TD]
47​
[/TD]
[TD]
82​
[/TD]
[TD]
74​
[/TD]
[TD]
60​
[/TD]
[TD]
12​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
105633​
[/TD]
[TD]
STU​
[/TD]
[TD]
57​
[/TD]
[TD]
50​
[/TD]
[TD]
91​
[/TD]
[TD]
59​
[/TD]
[TD]
56​
[/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
titamagochang said:
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
[TABLE="width: 896"]
<tbody>[TR]
[TD="class: xl66, width: 64"]ID[/TD]
[TD="class: xl66, width: 64"]STORE[/TD]
[TD="class: xl66, width: 64"]MILK[/TD]
[TD="class: xl66, width: 64"]BREAD[/TD]
[TD="class: xl66, width: 64"]MILK[/TD]
[TD="class: xl66, width: 64"]ORANGE[/TD]
[TD="class: xl66, width: 64"]COFFEE[/TD]
[TD="class: xl66, width: 64"]TEA[/TD]
[TD="class: xl66, width: 64"]BREAD[/TD]
[TD="class: xl66, width: 64"]COFFEE[/TD]
[TD="class: xl66, width: 64"]MILK[/TD]
[TD="class: xl66, width: 64"]BREAD[/TD]
[TD="class: xl66, width: 64"]ORANGE[/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]103279[/TD]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105629[/TD]
[TD="class: xl66"]DEF[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]24[/TD]
[TD="class: xl66"]56[/TD]
[TD="class: xl66"]24[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]19[/TD]
[TD="class: xl66"]22[/TD]
[TD="class: xl66"]25[/TD]
[TD="class: xl66"]21[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105631[/TD]
[TD="class: xl66"]GHI[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"]67[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]106236[/TD]
[TD="class: xl66"]JKL[/TD]
[TD="class: xl66"]67[/TD]
[TD="class: xl66"]89[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]39[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]106246[/TD]
[TD="class: xl66"]MNO[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl66"]32[/TD]
[TD="class: xl66"]43[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105696[/TD]
[TD="class: xl66"]PQR[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]32[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]28[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]34[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105633[/TD]
[TD="class: xl66"]STU[/TD]
[TD="class: xl66"]18[/TD]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"]16[/TD]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]56[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]23[/TD]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]78[/TD]
[TD="class: xl66, align: right"]19[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to SUM the rows and store it in the subtotal table below, i put the table in sheet2 :
[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl66, width: 64"]ID[/TD]
[TD="class: xl66, width: 64"]STORE[/TD]
[TD="class: xl66, width: 64"]MILK[/TD]
[TD="class: xl66, width: 64"]BREAD[/TD]
[TD="class: xl66, width: 64"]ORANGE[/TD]
[TD="class: xl66, width: 64"]COFFEE[/TD]
[TD="class: xl66, width: 64"]TEA[/TD]
[/TR]
[TR]
[TD="class: xl65"]103279[/TD]
[TD="class: xl66"]ABC[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105629[/TD]
[TD="class: xl66"]DEF[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105631[/TD]
[TD="class: xl66"]GHI[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]106236[/TD]
[TD="class: xl66"]JKL[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]106246[/TD]
[TD="class: xl66"]MNO[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105696[/TD]
[TD="class: xl66"]PQR[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]105633[/TD]
[TD="class: xl66"]STU[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

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 :)
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
Aladin Akyurek said:
A:N of Sheet1 houses the data which must be processed.

[...]
Click to expand...

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr]
[tr][td]
1​
[/td][td] ID[/td][td] STORE[/td][td] MILK[/td][td] BREAD[/td][td] ORANGE[/td][td] COFFEE[/td][td] TEA[/td][/tr]


[tr][td]
2​
[/td][td] 103279[/td][td] ABC[/td][td] 43[/td][td] 65[/td][td] 59[/td][td] 30[/td][td] 15[/td][/tr]


[tr][td]
3​
[/td][td] 105629[/td][td] DEF[/td][td] 80[/td][td] 88[/td][td] 77[/td][td] 43[/td][td] 13[/td][/tr]


[tr][td]
4​
[/td][td] 105631[/td][td] GHI[/td][td] 45[/td][td] 121[/td][td] 29[/td][td] 29[/td][td] 16[/td][/tr]


[tr][td]
5​
[/td][td] 106236[/td][td] JKL[/td][td] 113[/td][td] 129[/td][td] 30[/td][td] 53[/td][td] 16[/td][/tr]


[tr][td]
6​
[/td][td] 106246[/td][td] MNO[/td][td] 79[/td][td] 93[/td][td] 27[/td][td] 44[/td][td] 16[/td][/tr]


[tr][td]
7​
[/td][td] 105696[/td][td] PQR[/td][td] 47[/td][td] 82[/td][td] 74[/td][td] 60[/td][td] 12[/td][/tr]


[tr][td]
8​
[/td][td] 105633[/td][td] STU[/td][td] 57[/td][td] 50[/td][td] 91[/td][td] 59[/td][td] 56[/td][/tr]
[/table]

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Multiple answer for particular timesets and making a box if all relevant data matches
Replies
7
Views
145
myall_blues
myall_blues
J
  • Question Question
Sum only when time overlaps
Replies
1
Views
274
awoohaw
awoohaw
S
  • Solved
Transpose Data into multiple Columns
Replies
8
Views
300
kvsrinivasamurthy
K
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
258
tezza
tezza
R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
108
Rex987456
R

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top