SumIF with range across columns and rows

[unirSEO]

Hello everyone.

I am trying to do a SUMIF where the range consists of multiple columns and rows rather than just a single column.

for example:

	A	B	C	D
1	Store 1	Store 2	Store 3	Store 4
2	London	Chicago	Paris	5
3	Paris	NY	Ottawa	18
4	Edinburgh	Ottawa	Paris	21
5	Edinburgh	Ottawa	Paris	9
6	Chicao	London	Paris	46
7	NY	Berlin	LA	0
8	Wellington	Montreal	Paris	44
9	Berlin	Berlin	Paris	18
10	Ottawa	Paris	Ottawa	7
11	London	Paris	London	12
12	Madrid	London	London	6

<tbody>
</tbody>

I want to add together all the Stock for my London stores. Note that the last two rows has London twice.
The sum of that stock is 87 (5+46+12+12+6+6)

I have been messing around with index/match, with CONCATENATE on the stores and using a FIND with a SUMIF, but so far I can't figure out a way of doing it.

But in the end I want a nice table like this-

	F	G
1	City	Sum of Stock
2	Berlin	36
3	Chicago	51
4	Edinburgh	30
5	London	87
6	LA	0
7	Madrid	6
8	Montreal	44
9	NY	18
10	Ottawa	62
11	Paris	162
12	Wellington	44

<tbody>
</tbody>

Any ideas anyone?

Thanks!

 

[wideboydixon]

Multiple SUMIF is the most straightforward solution:

Book1
	A	B	C	D	E	F	G
1	Store 1	Store 2	Store 3	Store 4		City	Sum of Stock
2	London	Chicago	Paris	5		Berlin	36
3	Paris	NY	Ottawa	18		Chicago	51
4	Edinburgh	Ottawa	Paris	21		Edinburgh	30
5	Edinburgh	Ottawa	Paris	9		London	87
6	Chicago	London	Paris	46		LA	0
7	NY	Berlin	LA	0		Madrid	6
8	Wellington	Montreal	Paris	44		Montreal	44
9	Berlin	Berlin	Paris	18		NY	18
10	Ottawa	Paris	Ottawa	7		Ottawa	62
11	London	Paris	London	12		Paris	180
12	Madrid	London	London	6		Wellington	44
Sheet1

Cell Formulas
Range	Formula
G2	=SUMIF($A$2:$A$12,$F2,$D$2:$D$12)+SUMIF($B$2:$B$12,$F2,$D$2:$D$12)+SUMIF($C$2:$C$12,$F2,$D$2:$D$12)

WBD

 

[unirSEO]

Thanks WBD.

I was trying to resolve it with a single expression, but that way (which hadn't ocurred to me) is ace!

thanks very much.

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​
1​	Store 1	Store 2	Store 3	Store 4		City	Sum of Stock
2​	London	Chicago	Paris	5​		Berlin	36​
3​	Paris	NY	Ottawa	18​		Chicago	51​
4​	Edinburgh	Ottawa	Paris	21​		Edinburgh	30​
5​	Edinburgh	Ottawa	Paris	9​		London	87​
6​	Chicago	London	Paris	46​		LA	0​
7​	NY	Berlin	LA	0​		Madrid	6​
8​	Wellington	Montreal	Paris	44​		Montreal	44​
9​	Berlin	Berlin	Paris	18​		NY	18​
10​	Ottawa	Paris	Ottawa	7​		Ottawa	62​
11​	London	Paris	London	12​		Paris	180​
12​	Madrid	London	London	6​		Wellington	44​

<tbody>
</tbody>

g2=SUMPRODUCT(($A$2:$C$12=$F2)*($D$2:$D$12))