SUMPRODUCT with multiple criteria and arrays

[fishewb]

	1/1/2019	2/1/2019	3/1/2019	4/1/2019	5/1/2019	6/1/2019	7/1/2019
Product 1	0	0	0	0	0	0	0
Product 2	0	0	0	0	1	0	1
Product 3	0	1	1	2	0	0	0
Product 1	0	0	0	0	0	0	0
Product 2	0	0	0	0	0	0	0
Product 2	0	0	0	0	0	0	0
Product 1	0	0	0	0	0	0	0
Product 2	0	1	1	1	5	32	20
Product 3	60	45	49	40	62	41	69

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

Looking for help to get this SUMPRODUCT to work, using excel 2016

SUMPRODUCT((A2:A10="Product 1")*(A2:A10="Product 3")*(B1:H1=D1),B2:H10)

Thanks

 

[fishewb]

	A	B	C	D	E	F	G	H
1		1/1/2019	2/1/2019	3/1/2019	4/1/2019	5/1/2019	6/1/2019	7/1/2019
2	Product 1	0	0	0	0	0	0	0
3	Product 2	0	0	0	0	1	0	1
4	Product 3	0	1	1	2	0	0	0
5	Product 1	0	0	0	0	0	0	0
6	Product 2	0	0	0	0	0	0	0
7	Product 2	0	0	0	0	0	0	0
8	Product 1	0	0	0	0	0	0	0
9	Product 2	0	1	1	1	5	32	20
10	Product 3	60	45	49	40	62	41	69

<tbody>
</tbody>

Looking for help to get this SUMPRODUCT to work, using excel 2016

SUMPRODUCT((A2:A10="Product 1")*(A2:A10="Product 3")*(B1:H1=D1),B2:H10)

Thanks

Sorry my table was incomplete. Added column and row references.

 

[Fluff]

Rather than supplying a formula that doesn't work, can you please explain (in words) what you are trying to do.

 

[Marcelo Branco]

Something like this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

01/01/2019​

02/01/2019​

03/01/2019​

04/01/2019​

05/01/2019​

06/01/2019​

07/01/2019​

Products​

Date​

Result​

2​

Product 1​

0​

0​

0​

0​

0​

0​

0​

Product 1​

03/01/2019​

50​

3​

Product 2​

0​

0​

0​

0​

1​

0​

1​

Product 3​

4​

Product 3​

0​

1​

1​

2​

0​

0​

0​

5​

Product 1​

0​

0​

0​

0​

0​

0​

0​

6​

Product 2​

0​

0​

0​

0​

0​

0​

0​

7​

Product 2​

0​

0​

0​

0​

0​

0​

0​

8​

Product 1​

0​

0​

0​

0​

0​

0​

0​

9​

Product 2​

0​

1​

1​

1​

5​

32​

20​

10​

Product 3​

60​

45​

49​

40​

62​

41​

69​

Criteria in columns J:K

Formula L2
=SUMPRODUCT(SUMIF(A$2:A$10,J$2:J$3,INDEX(B$2:H$10,0,MATCH(K2,B$1:H$1,0))))

M.

 

[fishewb]

Rather than supplying a formula that doesn't work, can you please explain (in words) what you are trying to do.

Apologies, I'm new to posting.

I am trying to write utilize a function that can find more than one criteria in the same "column A" and sum all of each criteria found based on a particular month in row 1. In this example I'm looking for all of "Product 3" and "Product 2" in "3/1/2019"

 

[fishewb]

Something like this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

01/01/2019​

02/01/2019​

03/01/2019​

04/01/2019​

05/01/2019​

06/01/2019​

07/01/2019​

Products​

Date​

Result​

2​

Product 1​

0​

0​

0​

0​

0​

0​

0​

Product 1​

03/01/2019​

50​

3​

Product 2​

0​

0​

0​

0​

1​

0​

1​

Product 3​

4​

Product 3​

0​

1​

1​

2​

0​

0​

0​

5​

Product 1​

0​

0​

0​

0​

0​

0​

0​

6​

Product 2​

0​

0​

0​

0​

0​

0​

0​

7​

Product 2​

0​

0​

0​

0​

0​

0​

0​

8​

Product 1​

0​

0​

0​

0​

0​

0​

0​

9​

Product 2​

0​

1​

1​

1​

5​

32​

20​

10​

Product 3​

60​

45​

49​

40​

62​

41​

69​

<tbody>
</tbody>

Criteria in columns J:K

Formula L2
=SUMPRODUCT(SUMIF(A$2:A$10,J$2:J$3,INDEX(B$2:H$10,0,MATCH(K2,B$1:H$1,0))))

M.

Thank you, this does work. Is it possible to simplify the functions and only use SUMPRODUCT?

 

[Fluff]

If you just want sumproduct, try

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1		01/01/2019	01/02/2019	01/03/2019	01/04/2019	01/05/2019	01/06/2019	01/07/2019			01/03/2019
2	Product 1	0	0	0	0	0	0	0		Product 1	50
3	Product 2	0	0	0	0	1	0	1		Product 3
4	Product 3	0	1	1	2	0	0	0
5	Product 1	0	0	0	0	0	0	0
6	Product 2	0	0	0	0	0	0	0
7	Product 2	0	0	0	0	0	0	0
8	Product 1	0	0	0	0	0	0	0
9	Product 2	0	1	1	1	5	32	20
10	Product 3	60	45	49	40	62	41	69
Dashboard

Cell Formulas
Range	Formula
K2	=SUMPRODUCT(((A2:A10=J2)+(A2:A10=J3))*(B1:H1=K1),B2:H10)

 

[Marcelo Branco]

Thank you, this does work. Is it possible to simplify the functions and only use SUMPRODUCT?

I believe the formula i've suggested should be faster (more efficient) than other that uses SUMPRODUCT only.

M.

 

[fishewb]

Thank you!

 

[Fluff]

You're welcome, but as Marcelo has said, his formula is a better way of doing it.