# SUMIFS doesnot work with VSTACK and CHOOSECOLS?



## ErikHorsthuis (Oct 22, 2022)

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 (Oct 22, 2022)

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


----------



## ErikHorsthuis (Oct 22, 2022)

JvdV said:


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


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


----------



## Fluff (Oct 23, 2022)

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


----------



## Mehmet23 (Oct 23, 2022)

[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 (Oct 23, 2022)

Formula B2

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


----------



## Fluff (Oct 23, 2022)

What has that got to do with using sumifs?


----------



## Mehmet23 (Oct 23, 2022)

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 (Oct 23, 2022)

Mehmet23 said:


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


Mehmet23 said:


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


Actually they do.


----------



## fatalcry (Yesterday at 9:36 AM)

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.


----------

