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]

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)

Thank You!!
But my *ACTUAL* group is a lot more larger then my example, so if I use this method, the function would be like too long for anyone to read.
It's very helpful nevertheless, bless your soul, my friend.

 

[awoohaw]

i'm not sure what you mean. YOu would just need to update the cell references in the formulas. I would not be additional calculations in the formulas The only change would be a row number, from 10, to maybe 1000? that is all.

 

[YuanChen0824]

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

View attachment 87213
Yes!!! This is it! WOW you are a magician!
But why the functions you guys posted with google sheets wouldn't work on my excel 365?
However this line of function is exactly what I'm looking for!

I just want you know you made a desperate worker really, really happy : )

 

[YuanChen0824]

i'm not sure what you mean. YOu would just need to update the cell references in the formulas. I would not be additional calculations in the formulas The only change would be a row number, from 10, to maybe 1000? that is all.

Sorry, I mean my group is like 200+, and your solution is great!
I could read your function wrong, but this particular line:

Range	Cell Formulas	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)

It seems like If I need to add a new source to the group, I will have to change it into:
=SUMPRODUCT(((--($B$2:$B$7=$B11))+(--($B$2:$B$7=$B10)+(--($B$2:$B$7=$B12)))*(--(D$9=$C$2:$C$7))*$D$2:$D$7)
something like that, right?
The functions you provided is simple and effective, but when it comes to some large group, It become quite hard to use.
Please correct me if I made some silly mistake, I'm new to this.

 

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

 

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

 

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