SUMPRODUCT with dynamic arrays

I have 2 arrays.
1st array has 10 rows and 5 columns The row labels aren't unique.
2nd array has 10 rows with the same labels as 1st array but has only 1 column.
Now my output table has 4 rows which are unique labels from the 1st 2 arrays and 5 columns with same headers as 1st row. I need to get a sumproduct of 1st array and 2nd array. I need a dynamic formula so that 1 formula in the 1st cell gives results for the whole table.
I have given it a try in the attached excel (Cell C17), however, it's giving me value errors.

=MAKEARRAY(COUNTA(B17#), COUNTA(C16#), LAMBDA(name,subject, SUMPRODUCT(J4# * (I4# = INDEX(B17#, name)), C4# * (B4# = INDEX(B17#, name)) * (C3# = INDEX(C16#, subject)))))

Are you still using Excel 2013? MAKEARRAY is a 365 function - please update your forum profile.

C17:

Please Login or Register  to view this content.

Does it work?

See below:

Patience, please!

try this

=MAKEARRAY(COUNTA(B17#), COUNTA(C16#), LAMBDA(name,subject, SUMPRODUCT(J4# * (I4# = INDEX(B17#, name))*(C4# * (B4# = INDEX(B17#, name)) * (C3# = INDEX(C16#, subject))))))

PS. Thanks @Ali, I forgot to modify my test formula.

That returns 625 for every cell.

But this seems to work:

=MAKEARRAY(COUNTA(B17#), COUNTA(C16#), LAMBDA(name,subject, SUMPRODUCT(J4# * (I4# = INDEX(B17#, name))*(C4# * (B4# = INDEX(B17#, name)) * (C3# = INDEX(C16#, 1, subject))))))

And this will generate the whole table:

=LET(r,UNIQUE(B3:B13),c,C3#,HSTACK(r,VSTACK(c,MAKEARRAY(COUNTA(B17#),COUNTA(C16#),LAMBDA(name,subject,SUMPRODUCT(J4#*(I4#=INDEX(B17#,name))*(C4#*(B4#=INDEX(B17#,name))*(C3#=INDEX(C16#,1,subject)))))))))

PS. Thanks @Ali, I forgot to modify my test formula.

You're welcome! I had just almost arrived at the same solution myself.

Solutions seen.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Please try

=MMULT(N(B17#=TOROW(B4#)),C4#*J4#)

LoL. Far better than my effort, which was:

=DROP(REDUCE(0,B17#,LAMBDA(y,z,VSTACK(y,BYCOL(IF(B4#=z,C4#*J4#,0),LAMBDA(x,SUM(x)))))),1)

Thanks. I got what i was looking for. Also, you provided alternative formulas. Good learning for me. thanks again.

Glad to have helped.