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