|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Has anyone successfully used a SUM(SUMIFS) formula in spreadsheets?

Answered
Request Demo
Related to spreadsheet functions

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?

2

Comments

8 comments

Date Votes
  • Official comment
    Mike Davis

    Hi all!

    Wanted to share here that ARRAY formulas are now supported in Workiva. More information can be found here on our Help site. Thanks for your feedback and patience along the way. We couldn't have done it without you!

  • Mike Davis
    • Edited

    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"))/1000000

    I 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
  • Kellie Volpicelli

    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
  • Kellie Volpicelli

    Correction on my above formula =SUM(SUMIF($D:$D,{"123456","789101"},$C:$C)), sorry again! This is what I am trying to accomplish.

     

    0
  • Mike Davis
    • Edited

    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
  • Kellie Volpicelli

    Mike, yes that does make sense, thank you for the feedback!

    0
  • Amy Jordan

    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
  • MacKenzie Schrotenboer

    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

Please sign in to leave a comment.