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...

 

[fjns]

Hi, see the linked file for a possible solution...

The formula used in the table:
=SUMPRODUCT((B$2=Sheet1!$C$2:$C$7)*(Sheet1!$D$2:$D$7)*(IFERROR(MATCH((Sheet1!$B$2:$B$7),$I4:$I5,0),0)>0))

Arrays.xlsx

 

[awoohaw]

Something like this:

mr excel questions 13.xlsm
	A	B	C	D
1		NAME	TIME	NUMB
2		ABC	2023-03-10	26
3		ABC	2023-03-11	32
4		ABC	2023-03-12	48
5		DEF	2023-03-13	44
6		DEF	2023-03-10	128
7		ZZZ	2023-03-15	98
8
9			ALL	2023-03-10
10		ABC	106	26
11		DEF	172	128
12		ABC & DEF	278	154
Sheet13

Cell Formulas
Range	Formula
C10	C10	=SUMPRODUCT(--($B$2:$B$7=$B10)*$D$2:$D$7)
D10:D11	D10	=SUMPRODUCT(--($B$2:$B$7=$B10)*(--(D$9=$C$2:$C$7))*$D$2:$D$7)
C11	C11	=SUMPRODUCT(--($B$2:$B$7=B11)*$D$2:$D$7)
C12	C12	=SUMPRODUCT(((--($B$2:$B$7=$B11))+(--($B$2:$B$7=$B10)))*$D$2:$D$7)
D12	D12	=SUMPRODUCT(((--($B$2:$B$7=$B11))+(--($B$2:$B$7=$B10)))*(--(D$9=$C$2:$C$7))*$D$2:$D$7)

 

[Peter_SSs]

But my current function can't be used when I want it check a group of names(ABC & DEF, but not ZZZ)

What would your results look like if you were doing this with the original sample data above?

 

[fjns]

Hi, I created a new table...

ArraysNew.xlsx

 

[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))

 

[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]

OMG!!!!!!
Thank You guys soo much! (Also sorry for not responding for days, I was busy)
I'll go and try everything y'all offer me.
Will reply soon