sum in excel based on multiple criteria for rows and columns

[dinakar]

Hi for the sample table below in excel:

	New York	Japan	China	Chicago
Product A	3	5	1	2
Product B	9	6	7	4
Product C	2	9	1	4
Product D	1	3	2	5

<tbody>
</tbody>

	North America (New york &Chicago)	Asia(China&Japan
Product A
Product B
Product C&D

<tbody>
</tbody>

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]

Row\Col	A​	B​	C​	D​	E​
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​		North America	Asia
8​		New YorK	China
9​		Chicago	Japan
10​
11​	Product A	5​	6​
12​	Product B	13​	13​
13​	Product C	6​	10​
14​	Product D	6​	5​

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)

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Continent​	North America​	Asia​	Asia​	North America​		P1​	P2​	North America​	Asia​
2​	City/Country​	New York​	Japan​	China​	Chicago​		Product A​		5​	6​
3​	Product A​	3​	5​	1​	2​		Product B​		13​	13​
4​	Product B​	9​	6​	7​	4​		Product C​	Product D​	12​	15​
5​	Product C​	2​	9​	1​	4​
6​	Product D​	1​	3​	2​	5​
7​

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?

Row\Col	A​	B​	C​	D​	E​
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​			North America	Asia
8​			New YorK	China
9​			Chicago	Japan
10​
11​	Product A		5​	6​
12​	Product B		13​	13​
13​	Product C	Product D	12​	15​
14​

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