Let Function Failure

[nmmounir]

Hello Everyone,

I encountered issues with Let function especially after the 9th variable. I have 2 tables that contains 4 columns. Products' names are all in column 1 and some products are common between the two tables. I need to stack the two tables up, (I do not have VStack) so I am using the index function with the area number (area number is 2) to stack 2 columns only of the two tables.
After that I will do SUMIFS. to accomplish this, I used the Let function and everything was perfectly fine until the final step where the SUMIFS function was used.
Variable f result in cell D14 is :
=LET(a,B3:E11,b,G3:J11,c,MOD(SEQUENCE((2*ROWS(a)),,0),ROWS(a))+1,d,INT(SEQUENCE(2*ROWS(a),,0)/ROWS(a))+1,
e,INDEX((a,b),
c,{1,4},d),
f,FILTER(e,INDEX(e,,1)<>0),f)

so up to f variable, everything is fine, next step is to use SUMIFS which is what I i did in cell B14 as per below but I got a value error. This value error happens to me a lot when I use the Let function after the 9th or 10th Variable.

=LET(a,B3:E11,b,G3:J11,c,MOD(SEQUENCE((2*ROWS(a)),,0),ROWS(a))+1,d,INT(SEQUENCE(2*ROWS(a),,0)/ROWS(a))+1,
e,INDEX((a,b),c,{1,4},d),f,FILTER(e,INDEX(e,,1)<>0),g,INDEX(f,,2),h,INDEX(f,,1),i,UNIQUE(INDEX(f,,1)),SUMIFS(g,h,i))

Can anyone help resolve this puzzle.

 

[Fluff]

You cannot use sumifs on an array, it only works on a range.

 

[nmmounir]

You cannot use sumifs on an array, it only works on a range.

Can I use Sumproduct instead of Sumifs to get the correct answer inside the Let function ?

 

[Fluff]

No, but you could try

MMULT(--(i=TRANSPOSE(h)),g)

 

[nmmounir]

No, but you could try

MMULT(--(i=TRANSPOSE(h)),g)

Ok will try it out and let u know

No, but you could try

MMULT(--(i=TRANSPOSE(h)),g)

A very smart solution. It worked perfectly. Thank you very much for your help

 

[Anonymous1378]

No, but you could try

MMULT(--(i=TRANSPOSE(h)),g)

Out of curiosity, would this approach work with more than 1 SUMIFS criteria? Or would it be up to the new lambda functions?

 

[Fluff]

Glad to help & thanks for the feedback.

@Anonymous1378 yes it can do more than 1 criteria & as the OP doesn't have VSTACK they probably don't have LAMBDA either.