Sumproduct Help

[charlesloves2read]

Hi,

In Column A, I have two customers, A and B.
In Column B, I have 4 factory locations, North, South, East, West.
In Column C, I have Revenue.

How do i use Sumproduct to obtain the Total Revenue for Customer A in Factory Location North AND South.

I only know how to obtain Total Revenue for Customer A in ONE Factory Location.

Thanks!

This is a simplified example of an exercise i am supposed to do at work.

 

[T. Valko]

Is the match syntax wrong? It says lookupvalue first before lookuparray but your is the other way around

No, the MATCH syntax is correct.

Book1
	A	B	C	D	E	F
2	B	West	52	_	Formula 1	Formula 2
3	B	South	80	_	338	338
4	A	North	38	_	_	_
5	B	West	15	_	_	_
6	B	East	18	_	_	_
7	B	South	17	_	_	_
8	A	North	46	_	_	_
9	B	South	36	_	_	_
10	B	South	11	_	_	_
11	A	South	98	_	_	_
12	B	East	91	_	_	_
13	B	East	18	_	_	_
14	B	North	26	_	_	_
15	A	South	56	_	_	_
16	B	South	53	_	_	_
17	A	West	68	_	_	_
18	A	South	45	_	_	_
19	A	South	55	_	_	_
20	B	South	8	_	_	_
Sheet1

Formula 1:

=SUMPRODUCT(--(A2:A20="A"),--(ISNUMBER(MATCH(B2:B20,{"North","South"},0))),C2:C20)

Formula 2:

=SUMPRODUCT(--(A2:A20="A"),--(RIGHT(B2:B20,2)="th"),C2:C20)

 

[charlesloves2read]

No, the MATCH syntax is correct.

Book1
*	A	B	C	D	E	F
2	B	West	52	_	Formula 1	Formula 2
3	B	South	80	_	338	338
4	A	North	38	_	_	_
5	B	West	15	_	_	_
6	B	East	18	_	_	_
7	B	South	17	_	_	_
8	A	North	46	_	_	_
9	B	South	36	_	_	_
10	B	South	11	_	_	_
11	A	South	98	_	_	_
12	B	East	91	_	_	_
13	B	East	18	_	_	_
14	B	North	26	_	_	_
15	A	South	56	_	_	_
16	B	South	53	_	_	_
17	A	West	68	_	_	_
18	A	South	45	_	_	_
19	A	South	55	_	_	_
20	B	South	8	_	_	_
Sheet1

Formula 1:

=SUMPRODUCT(--(A2:A20="A"),--(ISNUMBER(MATCH(B2:B20,{"North","South"},0))),C2:C20)

Formula 2:

=SUMPRODUCT(--(A2:A20="A"),--(RIGHT(B2:B20,2)="th"),C2:C20)

Hmmm.... okay How can I get the curly bracket out? I am manually keying it in. and it is only taking the "North" values

 

[T. Valko]

Hmmm.... okay How can I get the curly bracket out? I am manually keying it in. and it is only taking the "North" values

We need the squiggly brackets. They define an array constant.

I'm not sure what you mean by: it is only taking the "North" values.

 

[charlesloves2read]

We need the squiggly brackets. They define an array constant.

I'm not sure what you mean by: it is only taking the "North" values.

Hi Biff,

Yes, I got it already. Thanks so much for your help and patience!

 

[T. Valko]

Hi Biff,

Yes, I got it already. Thanks so much for your help and patience!

Good deal. Thanks for the feedback!