SUMIFS doesnot work with VSTACK and CHOOSECOLS?

[ErikHorsthuis]

I am testing the new dynamic array functions (CHOOSECOLS and VSTACK) and I am trying to combine them with SUMIFS. I am trying to display in cell C2:D22 with SUMIFS the number of kudos from the respective student and sport. However, Excel does not allow up this formula. If I just run the SUMIFS formula on a table then of course it works. I'm afraid I'm overlooking something. Does anyone see what I am doing wrong? Thanks! I know that there are all kind of other approaches to solve this, I just want to know what is wrong in the formula.

=SUMIFS(CHOOSECOLS(VSTACK(Cycling,Running),4),CHOOSECOLS(VSTACK(Cycling,Running),1),A2#,CHOOSECOLS(VSTACK(Cycling,Running),3),C1#)

 

[JvdV]

`SUMIFS()` 1st parameter needs to be a range reference.

 

[ErikHorsthuis]

`SUMIFS()` 1st parameter needs to be a range reference.

CHOOSECOLS(VSTACK(Cycling,Running),4) is a range or not?

 

[Fluff]

No both choosecols & vtstack return an array not a range.

 

[Mehmet23]

[KOD=vba]LET(m,VSTACK(F2:H6,J2:L6),DÜNYE YUKARI(A2:A6&B1:C1,INDEX(m,,1)&INDEX(m,,2),INDEX(m,,3) ))[/KOD]

 

[Mehmet23]

Formula B2

LET(m,VSTACK(F2:H6,J2:L6),XLOOKUP(A2:A6&B1:C1,INDEX(m,,1)&INDEX(m,,2),INDEX(m,,3)))

 

[Fluff]

What has that got to do with using sumifs?

 

[Mehmet23]

I think the person asking the question is asking the wrong question. Similar names do not appear in the G-J and L-O charts. Does he/she need to use sumifs..

 

[Fluff]

I think the person asking the question is asking the wrong question.

I would think that the OP knows what he/she is trying to do better than you do.

Similar names do not appear in the G-J and L-O charts.

Actually they do.

 

[fatalcry]

I encountered this issue also but this was my solution matching your formula:

=map(a2#,c1#,lambda(a,b,sumproduct(--(choosecols(vstack(cycling,running),1)=a)*(choosecols(vstack(cycling,running),3)=b),choosecols(cycling,running),4)))))

Map function is needed here as you have a multi condition, a single condition would only need a byrow/bycol function.

Hope that works for you.