sum in excel based on multiple criteria for rows and columns

[dinakar]

Hi for the sample table below in excel:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]New York[/TD]
[TD]Japan[/TD]
[TD]China[/TD]
[TD]Chicago[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]2[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]North America (New york &Chicago)[/TD]
[TD]Asia(China&Japan[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product C&D[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have a destination table which is as the 2nd table where in need the results sum'd by rows and columns. I have tried Sumifs and Sumproducts but i am unable to figure that out. Any help is greatly appreciated.

 

[gvillepa]

is this what you want? a simple sum formula?

Book1
	A	B	C	D	E	F
1		New York	Japan	China	Chicago
2	Product A	3	5	1	2
3	Product B	9	6	7	4
4	Product C	2	9	1	4
5	Product D	1	3	2	5
6
7
8		North America (New york &Chicago)	Asia(China&Japan
9	Product A	5	6
10	Product B	13	13
11	Product C&D	12	15
Sheet1

Cell Formulas
Range	Formula
B9	=SUM(B2,E2)
B10	=SUM(B3,E3)
B11	=SUM(B4:B5,E4:E5)
C9	=SUM(C2:D2)
C10	=SUM(C3:D3)
C11	=SUM(C4:D5)

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]New York[/td][td]Japan[/td][td]China[/td][td]Chicago[/td][/tr]
[tr][td]

2​

[/td][td]Product A[/td][td]

3​

[/td][td]

5​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]
[tr][td]

3​

[/td][td]Product B[/td][td]

9​

[/td][td]

6​

[/td][td]

7​

[/td][td]

4​

[/td][/tr]
[tr][td]

4​

[/td][td]Product C[/td][td]

2​

[/td][td]

9​

[/td][td]

1​

[/td][td]

4​

[/td][/tr]
[tr][td]

5​

[/td][td]Product D[/td][td]

1​

[/td][td]

3​

[/td][td]

2​

[/td][td]

5​

[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td]North America[/td][td]Asia[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td]New YorK[/td][td]China[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td][/td][td]Chicago[/td][td]Japan[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]Product A[/td][td]

5​

[/td][td]

6​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]Product B[/td][td]

13​

[/td][td]

13​

[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]Product C[/td][td]

6​

[/td][td]

10​

[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]Product D[/td][td]

6​

[/td][td]

5​

[/td][td][/td][td][/td][/tr]
[/table]

In B11 enter, copy across to C11, and down:

=SUMPRODUCT(SUMIFS(INDEX($B$2:$E$5,MATCH($A11,$A$2:$A$5,0),0),$B$1:$E$1,B$8:B$9))

 

[Marcelo Branco]

Another formula (different data setup)


[Table="class: grid"][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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td="bgcolor:#B8CCE4"]

Continent​

[/td][td="bgcolor:#B8CCE4"]

North America​

[/td][td="bgcolor:#B8CCE4"]

Asia​

[/td][td="bgcolor:#B8CCE4"]

Asia​

[/td][td="bgcolor:#B8CCE4"]

North America​

[/td][td][/td][td]

P1​

[/td][td]

P2​

[/td][td="bgcolor:#B8CCE4"]

North America​

[/td][td="bgcolor:#B8CCE4"]

Asia​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td="bgcolor:#D9D9D9"]

City/Country​

[/td][td="bgcolor:#D9D9D9"]

New York​

[/td][td="bgcolor:#D9D9D9"]

Japan​

[/td][td="bgcolor:#D9D9D9"]

China​

[/td][td="bgcolor:#D9D9D9"]

Chicago​

[/td][td][/td][td]

Product A​

[/td][td][/td][td]

5​

[/td][td]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Product A​

[/td][td]

3​

[/td][td]

5​

[/td][td]

1​

[/td][td]

2​

[/td][td][/td][td]

Product B​

[/td][td][/td][td]

13​

[/td][td]

13​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Product B​

[/td][td]

9​

[/td][td]

6​

[/td][td]

7​

[/td][td]

4​

[/td][td][/td][td]

Product C​

[/td][td]

Product D​

[/td][td]

12​

[/td][td]

15​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Product C​

[/td][td]

2​

[/td][td]

9​

[/td][td]

1​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Product D​

[/td][td]

1​

[/td][td]

3​

[/td][td]

2​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in I2 copied across and down
=SUMPRODUCT(($B$1:$E$1=I$1)*ISNUMBER(MATCH($A$3:$A$6,$G2:$H2,0)),$B$3:$E$6)

M.

 

[dinakar]

Thank you Aladin,

Is there a way I can also sum by the product- For example The sum of Products C&D (Rows) and also have New York and Chicago sum?

 

[Aladin Akyurek]

Thank you Aladin,

Is there a way I can also sum by the product- For example The sum of Products C&D (Rows) and also have New York and Chicago sum?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]New York[/td][td]Japan[/td][td]China[/td][td]Chicago[/td][/tr]
[tr][td]

2​

[/td][td]Product A[/td][td]

3​

[/td][td]

5​

[/td][td]

1​

[/td][td]

2​

[/td][/tr]
[tr][td]

3​

[/td][td]Product B[/td][td]

9​

[/td][td]

6​

[/td][td]

7​

[/td][td]

4​

[/td][/tr]
[tr][td]

4​

[/td][td]Product C[/td][td]

2​

[/td][td]

9​

[/td][td]

1​

[/td][td]

4​

[/td][/tr]
[tr][td]

5​

[/td][td]Product D[/td][td]

1​

[/td][td]

3​

[/td][td]

2​

[/td][td]

5​

[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td]North America[/td][td]Asia[/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td]New YorK[/td][td]China[/td][td][/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td]Chicago[/td][td]Japan[/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]Product A[/td][td][/td][td]

5​

[/td][td]

6​

[/td][td][/td][/tr]
[tr][td]

12​

[/td][td]Product B[/td][td][/td][td]

13​

[/td][td]

13​

[/td][td][/td][/tr]
[tr][td]

13​

[/td][td]Product C[/td][td]Product D[/td][td]

12​

[/td][td]

15​

[/td][td][/td][/tr]
[tr][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Either in C11 enter, copy across, and down:

=SUMPRODUCT($B$2:$E$5,ISNUMBER(MATCH($A$2:$A$5,$A11:$B11,0))*ISNUMBER(MATCH($B$1:$E$1,C$8:C$9,0)))

like Marcelo Branco (post #4 ) suggested;

Or in C11 control+shift+enter, not just enter, copy across, and down:

=SUM(IF(ISNUMBER(MATCH($A$2:$A$5,$A11:$B11,0)),IF(ISNUMBER(MATCH($B$1:$E$1,C$8:C$9,0)),$B$2:$E$5)))