Sumproduct with Arrays of Different sizes

[YuanChen0824]

Hello, guys, I want some help with my excel functions
(Sorry I don't speak English so my post will likely be a challenge to read)
Here's the sheet (sheet 2) :

the function(on the Left):

	3/10/2023	3/11/2023	3/12/2023	3/13/2023	3/14/2023	3/15/2023
ABC	=SUMPRODUCT((B$2=Sheet1!$C$2:$C$7)*(Sheet2!$A3=Sheet1!$B$2:$B$7)*(Sheet1!$D$2:$D$7))	=SUMPRODUCT((C$2=Sheet1!$C$2:$C$7)*(Sheet2!$A3=Sheet1!$B$2:$B$7)*(Sheet1!$D$2:$D$7))	=SUMPRODUCT((D$2=Sheet1!$C$2:$C$7)*(Sheet2!$A3=Sheet1!$B$2:$B$7)*(Sheet1!$D$2:$D$7))	=SUMPRODUCT((E$2=Sheet1!$C$2:$C$7)*(Sheet2!$A3=Sheet1!$B$2:$B$7)*(Sheet1!$D$2:$D$7))	=SUMPRODUCT((F$2=Sheet1!$C$2:$C$7)*(Sheet2!$A3=Sheet1!$B$2:$B$7)*(Sheet1!$D$2:$D$7))	=SUMPRODUCT((G$2=Sheet1!$C$2:$C$7)*(Sheet2!$A3=Sheet1!$B$2:$B$7)*(Sheet1!$D$2:$D$7))

The other sheet I want to reference to (The sheet 1) :

I want my function return the corresponding value (which works fine)
But my current function can't be used when I want it check a group of names(ABC & DEF, but not ZZZ)

Please help? I've Try so many ways, I've read so many post, but none of them can help me...

 

[YuanChen0824]

LOVE YOU ALL

 

[awoohaw]

are you saying that you have many values that you want to include in the "OR" statement?
Please post some examples of the workbook where you input all the possible values you want in the OR statement.

 

[YuanChen0824]

are you saying that you have many values that you want to include in the "OR" statement?
Please post some examples of the workbook where you input all the possible values you want in the OR statement.

Unfortunately I can't do that, my workplace has some strict policy prohibiting me to upload any data, document or picture
But I can show you some example:

Example

Sheet1 Group 1,Group 2,Group 3,DATA name a1,name b1,name c1,time,name,number name a2,name b2,name c2,05/06/2001,name b2,12 name a3,name b3,name c3,05/06/2001,name b2,9 name a4,name b4,name c4,05/06/2001,name b1,120 name a5,name b5,name c5,05/06/2001,name a2,55 name a6,name b6,name c6,05/07/2001,...

docs.google.com

 

[YuanChen0824]

are you saying that you have many values that you want to include in the "OR" statement?
Please post some examples of the workbook where you input all the possible values you want in the OR statement.

I'm very grateful of your generous help,
I have the solution already, but if you want to discuss this furthermore, I'm with you.

 

[fjns]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.

 

[Peter_SSs]

I see that you already have a solution, but since you have version 365 you could also employ one of the new functions there (FILTER) rather than the older SUMPRODUCT.

YuanChen0824.xlsx
	B	C	D
1	NAME	TIME	NUM
2	ABC	10/03/2023	26
3	ZZZ	11/03/2023	32
4	ABC	12/03/2023	48
5	VWX	13/03/2023	44
6	DEF	14/03/2023	128
7	ZZZ	15/03/2023	98
8	JKL	12/03/2023	200
9
Sheet1

YuanChen0824.xlsx
	A	B	C	D	E	F	G
2		10/03/2023	11/03/2023	12/03/2023	13/03/2023	14/03/2023	15/03/2023
3	GROUP	26	0	248	44	128	0
4	ABC
5	DEF
6	GHI
7	JKL
8	MNO
9	PQR
10	STU
11	VWX
12
Sheet2

Cell Formulas
Range	Formula
B3:G3	B3	=SUM(FILTER(Sheet1!$D$2:$D$8,(Sheet1!$C$2:$C$8=B$2)*(IFNA(MATCH(Sheet1!$B$2:$B$8,$A$4:$A$11,0),0)),0))

 

[YuanChen0824]

I see that you already have a solution, but since you have version 365 you could also employ one of the new functions there (FILTER) rather than the older SUMPRODUCT.

YuanChen0824.xlsx
	B	C	D
1	NAME	TIME	NUM
2	ABC	10/03/2023	26
3	ZZZ	11/03/2023	32
4	ABC	12/03/2023	48
5	VWX	13/03/2023	44
6	DEF	14/03/2023	128
7	ZZZ	15/03/2023	98
8	JKL	12/03/2023	200
9
Sheet1

YuanChen0824.xlsx
	A	B	C	D	E	F	G
2		10/03/2023	11/03/2023	12/03/2023	13/03/2023	14/03/2023	15/03/2023
3	GROUP	26	0	248	44	128	0
4	ABC
5	DEF
6	GHI
7	JKL
8	MNO
9	PQR
10	STU
11	VWX
12
Sheet2

Cell Formulas
Range	Formula
B3:G3	B3	=SUM(FILTER(Sheet1!$D$2:$D$8,(Sheet1!$C$2:$C$8=B$2)*(IFNA(MATCH(Sheet1!$B$2:$B$8,$A$4:$A$11,0),0)),0))

Oh cool! I'll definitely give it a try
Does the new "Filter" function has better performance?
Because I'm dealing with some BIG data.

 

[Peter_SSs]

Does the new "Filter" function has better performance?
Because I'm dealing with some BIG data.

It may not be that good on very large data, but then neither will SUMPRODUCT.
SUMIFS is pretty efficient, so you may possibly find better performance with a structure like below, even though it involves a lot more formulas.
Each case is different and I have not tested any of these suggestions on large data, but at least you have some options to test.
I guess that another option might be vba if these all prove too slow.

YuanChen0824.xlsx
	A	B	C	D	E	F	G
2		10/03/2023	11/03/2023	12/03/2023	13/03/2023	14/03/2023	15/03/2023
3	GROUP	26	0	248	44	128	0
4	ABC	26	0	48	0	0	0
5	DEF	0	0	0	0	128	0
6	GHI	0	0	0	0	0	0
7	JKL	0	0	200	0	0	0
8	MNO	0	0	0	0	0	0
9	PQR	0	0	0	0	0	0
10	STU	0	0	0	0	0	0
11	VWX	0	0	0	44	0	0
Sheet3

Cell Formulas
Range	Formula
B3:G3	B3	=SUM(B4:B11)
B4:G11	B4	=SUMIFS(Sheet1!$D$2:$D$8,Sheet1!$C$2:$C$8,B$2,Sheet1!$B$2:$B$8,$A4)

 

[fjns]

Hi, I created the newer version of the table for your #13 message.

The new formula used in the new table:
=LET(i,IFERROR(MATCH($A2,Groups!$1:$1,0),0),c,CHAR(64+IFERROR(MATCH($A2,Groups!$1:$1,0),0)),n,(Data!$C$2:$C$11),t,(C$1=Data!$A$2:$A$11),IF(i=0,SUMPRODUCT(t*($A2=Data!$B$2:$B$11)*n),SUMPRODUCT(t*n*(IFERROR(MATCH((Data!$B$2:$B$11),INDIRECT("Groups!$"&c&":$"&c),0),0)>0))))

ArraysNewer.xlsx

 

[YuanChen0824]

It may not be that good on very large data, but then neither will SUMPRODUCT.
SUMIFS is pretty efficient, so you may possibly find better performance with a structure like below, even though it involves a lot more formulas.
Each case is different and I have not tested any of these suggestions on large data, but at least you have some options to test.
I guess that another option might be vba if these all prove too slow.

Thank You, this is really helpful.
I'm leaning VBA code, too. Maybe I'll try doing that.
If I have some new problem while coding (which I will, for sure)
I'll let you guys know, because this community is just so awesome.