Has anyone successfully used a SUM(SUMIFS) formula in spreadsheets?
I imported a spreadsheet into Wdesk and it has SUM(SUMIFS) formulas all over the place, but I can't seem to get them to work in spreadsheets. So currently I have #VALUE in all the cells. Does anyone know if Wdesk supports the use of this formula combination? Otherwise, I will have to add a bunch of SUMIF formulas to each other which will take me way too much time. Here is an example of one that is not working:
=SUM(SUMIFS('Essbase'!$E:$E,'Essbase'!$A:$A,"BECIA","123456"))/1000000
Any suggestions?
-
Hi Kellie!
Looking at that, it would seem that you are missing a second criteria range before the criteria "123456". Something like:
=SUM(SUMIFS('Essbase'!$E:$E,'Essbase'!$A:$A,"BECIA",CRITERIA_RANGE_HERE,"123456"))/1000000I was able to get a calculation applied that worked. Here's an example:
Let me know what follow-ups you have for me. Chat soon, and happy Friday!
0
Mike, the formula I sent you was a bad example, I apologize. What I am actually trying to do is sum multiple criteria, so if column D is 123456 or 789101, add the value in column C. In Excel it would be the below formula, but in spreadsheets the below formula gives #VALUE. I would expect the below formula to result in $144,890.72. It seems that in spreadsheets, it wants me to create individual sumif formulas for the 123456 and another for the 789101 and to add them together. Are you able to get a result if you try this? I am not able. Thank you Mike!
=SUM(SUMIF($C:$C,{"123456","789101"},$D:$D))
0
Correction on my above formula =SUM(SUMIF($D:$D,{"123456","789101"},$C:$C)), sorry again! This is what I am trying to accomplish.
0
Gotcha, Kellie. I see what you are trying to do there. I believe this fails in Workiva as Array formulas are not yet support (the portion of the SUMIF that is {"123456","789101"}).
A way around this would be to sum together multiple SUMIFS, such as...
=SUMIF(D:D,"123456",C:C)+SUMIF(D:D,"789101",C:C)
A SUMIFS would only work if the criteria were the same, like an AND function, but not for an OR type function. Does that make sense?
0
Mike, yes that does make sense, thank you for the feedback!
0
Would also like to see ability to do sum(sumifs). The work around of adding multiple sumifs statements together makes formulas longer and harder to follow.
0
Hi Amy Jordan,
Thank you for your feedback! I will spin up a ticket for you so that we can share your feedback with the Product Team.
0请先登录再写评论。
评论
7 条评论