I have a simple formula like: IFERROR(SUM(SUMIFS(TBL1[SUMCOLUMN],TBL1[ACTIVITY],{"0001","0002","0003"})),0) and it works.
But I want the {"0001","0002","0003"} part to be different based on the value of a column so I change the formula to:
IFERROR(SUM(SUMIFS(TBL1[SUMCOLUMN],TBL1[ACTIVITY],IF([@TYPE]="EHT",{"0001","0002","0005"},IF([@TYPE]="CT",{"0003","0004","0007"},"0010")))),0)
And it gives me values only based on the first array value. Example:
IF([@TYPE]="EHT",{"0001","0002","0005"} //Gives me sum of only where "0001" and ignores "0002","0005"
IF([@TYPE]="CT",{"0003","0004","0007"} //Gives me sum of only where "0003" and ignores "0004","0007"
I thought excel is converting the critera into text so I have played around with different methods like using char(34), different number of quotes, putting values in different cells and referencing but no help. Any idea what I am missing?
Sample tables: TBL1:
SUMCOLUMN ACTIVITY 5 0001 20 0002 50 0003 ... TBL2: TYPE TOTAL EHT SUMIFS GOES HERE CT SUMIFS GOES HERE OTHER SUMIFS GOES HERE
I don't know WHY it is doing that - it's very annoying. But in your particular case would this work:
=IFERROR(SUM(
IF([@TYPE]="EHT",SUMIFS(TBL1[SUMCOLUMN],TBL1[ACTIVITY],{"0001","0002","0005"}),0),
IF([@TYPE]="CT",SUMIFS(TBL1[SUMCOLUMN],TBL1[ACTIVITY],",{"0003","0004","0007"}),0),
SUMIFS(TBL1[SUMCOLUMN],TBL1[ACTIVITY],"0010")
),0)
Since SUMIFS returns an array of results and you are summing them, instead of trying to make the criteria conditional make the whole SUMIFS conditional.