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

 

[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

 

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