Sumproduct with Arrays of Different sizes

YuanChen0824

New Member
Joined
Mar 10, 2023
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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) :
1678430461205.png


the function(on the Left):
3/10/20233/11/20233/12/20233/13/20233/14/20233/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) :
1678430595699.png



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

Attachments

  • 1678430654992.png
    1678430654992.png
    4.8 KB · Views: 9

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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

Arrays.png
 
Upvote 1
Solution
Something like this:

mr excel questions 13.xlsm
ABCD
1NAMETIMENUMB
2ABC2023-03-1026
3ABC2023-03-1132
4ABC2023-03-1248
5DEF2023-03-1344
6DEF2023-03-10128
7ZZZ2023-03-1598
8
9ALL2023-03-10
10ABC10626
11DEF172128
12ABC & DEF278154
Sheet13
Cell Formulas
RangeFormula
C10C10=SUMPRODUCT(--($B$2:$B$7=$B10)*$D$2:$D$7)
D10:D11D10=SUMPRODUCT(--($B$2:$B$7=$B10)*(--(D$9=$C$2:$C$7))*$D$2:$D$7)
C11C11=SUMPRODUCT(--($B$2:$B$7=B11)*$D$2:$D$7)
C12C12=SUMPRODUCT(((--($B$2:$B$7=$B11))+(--($B$2:$B$7=$B10)))*$D$2:$D$7)
D12D12=SUMPRODUCT(((--($B$2:$B$7=$B11))+(--($B$2:$B$7=$B10)))*(--(D$9=$C$2:$C$7))*$D$2:$D$7)
 
Upvote 1
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?
 
Upvote 1
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
 
Upvote 0
Something like this:

mr excel questions 13.xlsm
ABCD
1NAMETIMENUMB
2ABC2023-03-1026
3ABC2023-03-1132
4ABC2023-03-1248
5DEF2023-03-1344
6DEF2023-03-10128
7ZZZ2023-03-1598
8
9ALL2023-03-10
10ABC10626
11DEF172128
12ABC & DEF278154
Sheet13
Cell Formulas
RangeFormula
C10C10=SUMPRODUCT(--($B$2:$B$7=$B10)*$D$2:$D$7)
D10:D11D10=SUMPRODUCT(--($B$2:$B$7=$B10)*(--(D$9=$C$2:$C$7))*$D$2:$D$7)
C11C11=SUMPRODUCT(--($B$2:$B$7=B11)*$D$2:$D$7)
C12C12=SUMPRODUCT(((--($B$2:$B$7=$B11))+(--($B$2:$B$7=$B10)))*$D$2:$D$7)
D12D12=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.
 
Upvote 0
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.
 
Upvote 0
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 : )
 
Upvote 0
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 FormulasFormula
C10C10=SUMPRODUCT(--($B$2:$B$7=$B10)*$D$2:$D$7)
D10:D11D10=SUMPRODUCT(--($B$2:$B$7=$B10)*(--(D$9=$C$2:$C$7))*$D$2:$D$7)
C11C11=SUMPRODUCT(--($B$2:$B$7=B11)*$D$2:$D$7)
C12C12=SUMPRODUCT(((--($B$2:$B$7=$B11))+(--($B$2:$B$7=$B10)))*$D$2:$D$7)
D12D12=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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top