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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Store 1[/TD]
[TD]Store 2[/TD]
[TD]Store 3[/TD]
[TD]Store 4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]London[/TD]
[TD]Chicago[/TD]
[TD]Paris[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Paris[/TD]
[TD]NY[/TD]
[TD]Ottawa[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Edinburgh[/TD]
[TD]Ottawa[/TD]
[TD]Paris[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Edinburgh[/TD]
[TD]Ottawa[/TD]
[TD]Paris[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Chicao[/TD]
[TD]London[/TD]
[TD]Paris[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]NY[/TD]
[TD]Berlin[/TD]
[TD]LA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Wellington[/TD]
[TD]Montreal[/TD]
[TD]Paris[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Berlin[/TD]
[TD]Berlin[/TD]
[TD]Paris[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Ottawa[/TD]
[TD]Paris[/TD]
[TD]Ottawa[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]London[/TD]
[TD]Paris[/TD]
[TD]London[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Madrid[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


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-

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]City[/TD]
[TD]Sum of Stock[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Berlin[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Chicago[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Edinburgh[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]London[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]LA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Madrid[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Montreal[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]NY[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Ottawa[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Paris[/TD]
[TD]162[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Wellington[/TD]
[TD]44[/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/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]Store 1[/TD]
[TD]Store 2[/TD]
[TD]Store 3[/TD]
[TD]Store 4[/TD]
[TD][/TD]
[TD]City[/TD]
[TD]Sum of Stock[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]London[/TD]
[TD]Chicago[/TD]
[TD]Paris[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]Berlin[/TD]
[TD]

36​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Paris[/TD]
[TD]NY[/TD]
[TD]Ottawa[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD]Chicago[/TD]
[TD]

51​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Edinburgh[/TD]
[TD]Ottawa[/TD]
[TD]Paris[/TD]
[TD]

21​

[/TD]
[TD][/TD]
[TD]Edinburgh[/TD]
[TD]

30​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Edinburgh[/TD]
[TD]Ottawa[/TD]
[TD]Paris[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD]London[/TD]
[TD]

87​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Chicago[/TD]
[TD]London[/TD]
[TD]Paris[/TD]
[TD]

46​

[/TD]
[TD][/TD]
[TD]LA[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]NY[/TD]
[TD]Berlin[/TD]
[TD]LA[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]Madrid[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Wellington[/TD]
[TD]Montreal[/TD]
[TD]Paris[/TD]
[TD]

44​

[/TD]
[TD][/TD]
[TD]Montreal[/TD]
[TD]

44​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Berlin[/TD]
[TD]Berlin[/TD]
[TD]Paris[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD]NY[/TD]
[TD]

18​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Ottawa[/TD]
[TD]Paris[/TD]
[TD]Ottawa[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD]Ottawa[/TD]
[TD]

62​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]London[/TD]
[TD]Paris[/TD]
[TD]London[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD]Paris[/TD]
[TD]

180​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Madrid[/TD]
[TD]London[/TD]
[TD]London[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD]Wellington[/TD]
[TD]

44​

[/TD]
[/TR]
</tbody>[/TABLE]


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