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

Sumproduct formula with multiple criteria

Beantwoord
4

Opmerkingen

21 opmerkingen

  • Mike

    Hi Yulia!

    I can say that yes, Workiva does support both IF and SUMPRODUCT formulas. Off hand I am not sure what the error is. I'm curious, what does the "--" after SUMPRODUCT do, and overall what are you looking to have this formula calculate?

    0
  • Yulia Oleynik

    Hi Mike,

    thank you for your input. "--" means that I compare two or more arrays with multiple criteria. It works in Excel.

    0
  • Gilbert Lindsay

    Old post, but I agree with Yulia that Workiva's SUMPRODUCT function does not support the same functionality as Excel or Google Sheets.

    See the below simplified example with fictitious numbers and how the function operates in Excel or Google Sheets. This does not operate the same in Workiva which results with #VALUE in cells C2 and C3 with an error message of "Operators cannot be applied to ranges."

    This functionality would provide a great level of utility to financial reporting functions within Workiva; limiting input sources and function work quarter to quarter.


    2
  • Nitika Rattan

    Checking in if there is a work-around for sumproduct with criterias? Another formula may be? Or a Wdata Query driven approach if source data is in different sheet?

    0
  • Pedro Andrade

    Hi,

    I have also tried to work using the same known functionallity of sumproduct mentioned by Gilbert Lindsay to sum based on criterias in a matrix, but it did not work out.

    Is there any news about when this functionallity would be available?

    0
  • Pedro Andrade

    I have just found another way to get the same functionallity of Sumproduct mentioned above... Just try to do this one:

    =SUM(INDEX("RANGE 1";MATCH("Lookup Value 1";"RANGE 2";0);MATCH("Lookup Value 2";"RANGE 3";0)))

    Where:

    • "RANGE 1" = sum range
    • "Lookup Value 1" = row name
    • "RANGE 2" = range of rows
    • "Lookup Value 2" = column name
    • "RANGE 3" = range of columns

    PS: MATCH returns the position of a given row or column and INDEX points to a coordinate in the matrix (Columns and Rows).

    0
  • Gilbert Lindsay

    Pedro Andrade Unfortunately, even with a simple table on a single Workiva worksheet, the SUM + INDEX/MATCH function does not work. This was another solution that I had attempted previously before the SUMPRODUCT example above.

    0
  • Pedro Andrade

    Hi Gilbert Lindsay

    Maybe you could share an example so we may understand how did you set your formula.

    I share an example below with the formula working just fine:

    0
  • Gilbert Lindsay

    Pedro Andrade In your example, it's hard to see the source information and how your formula is working. 

    Basic calculation drafted below; all housed in a single worksheet. This is representative to how I would use the function.

    0
  • Pedro Andrade

    Hi Lindsay,

    I have just rebuilt your example and I just noticed the formula I've found does not solve your issue, as it retrieves the first value that matches the criteria. Thereore, instead of retrieving 66 for Apples in 9/30/22, it retrieves 18.

    That been said, I guess the only possible formula would be the SUMPRODUCT you've written earlier, but it seems that complex way of writing it is not supported by Workiva yet. if it is essential to you, I would strongly suggest to post a Support ticket to them... it would be a great addition.

    In the other hand, another option would be retrieving the totals through creating a query. Not the best, but I guess is the only solution for now.

     

    PS: Just in case, you ever need to retrieve just 1 data per period (my case), the formula in your example above is well written but there is one minor error... there is a " ; " instead of a " , " before the 2nd MATCH. Try replacing it and it should work just fine:

    =SUM(INDEX($F$34:$I$38,MATCH($A34,$E$34:$E$38,0),MATCH(B$33,$F$33:$I$33,0)))

     

    0
  • Chris Popovici

    Is Workiva planning to add this functionality to the platform in order to right formulas like the one below?

    0
  • Courtnie Carver

    Hello Chris, 

    As of right now we have a ticket into our product team to consider implementing sumproduct formula with multiple criteria. I have created a ticket for you as well to be tied to that suggestion, it helps our product team to know how many people would benefit from something so they can fully consider feature ideas! 

    Additionally, I am curious if multiplying three separate SUMPRODUCT formulas by each other would work for what you are trying to do above. 

    Happy Friday!

    0
  • Alexander Bates

    Hi all - would like to bump this post to see if there has been any movement on the SUMPRODUCT formula with multiple criteria mentioned in the original post? Would love to see this implemented. We are changing GL systems & re-designing our spreadsheets. It would be great if we could build with this formula to provide a bit more functionality. 

    Thanks! 

    1
  • Mike

    Hi Alex!

    Thanks for the bump. 🤜 🤛

    There's not been any positive movement per se though this does remain on the roadmap to be done. I've shared your added insights and we'll keep you updated here going forward.

    0
  • Nick Giordano

    Hi, I am a new customer and tried to do this exact same thing in my spreadsheets. Was surprised it didn't work considering it is pretty basic Excel functionality. Since it was mentioned Workiva would like to know how many people this impacts, I figured I would mention something. Too bad to hear no movement has been made on this. 

    2
  • Ivan Lee

    also seeking for answers for this, please help!

    1
  • Mike

    Hi Ivan!

    I'm happy to get you added to our list of folks wanting this feature. That said, are you encountering an error within the application related to this functionality gap? Let me know how we can assist and what questions you have.

    0
  • Nava Calderon

    Looks like Workiva still doesn't support the Sumproduct function, can we be added to the list of clients who want this feature? Thanks!

    0
  • Mike

    You bet, Nava!

    0
  • Michael Shaw

    Also looking for Workiva to implement double unary operations within SUMPRODUCT. Is this in development pipeline at all?

    0
  • Mike

    Hello fellow Michael!

    Overall, additional support for functions missing in SUMPRODUCT is on the roadmap to be done. At this point it will be moved to 2024. Happy to get your feedback added and will keep you updated here on progress. Thanks for chiming in!

    0

U moet u aanmelden om een opmerking te plaatsen.