Sumproduct formula with multiple criteria
Con rispostaHi,
i uploaded the files where i use this formula: =IF(B4=0,0,SUMPRODUCT(--('2017 YTD'!$B$133:$B$1000">="&B1),--('2017 YTD'!$B$133:$B$1000"<="&B2),'2017 YTD'!$E$133:$E$1000)/B4)
However, it doesn't work. Does anyone know if this formula supported in the Workiva?
-
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
Hi Mike,
thank you for your input. "--" means that I compare two or more arrays with multiple criteria. It works in Excel.
0
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
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
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
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
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
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
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
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
Is Workiva planning to add this functionality to the platform in order to right formulas like the one below?
0
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
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
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
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
also seeking for answers for this, please help!
1
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
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
You bet, Nava!
0
Also looking for Workiva to implement double unary operations within SUMPRODUCT. Is this in development pipeline at all?
0
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
I would also hugely benefit from this formula functionality. We're halfway through 2024 now, any update on when it will be available?
0
I've added you to our update list for this functionality Becca Prestidge! As of timeline, nothing concrete yet - you can still expect it at some point in 2024.
0
I have the same issue. Looks like the SUMPRODUCT formula has not worked in Workiva for at least 4 years. Would really appreciate if you can get this working!
0
I hear ya! The good news is that this feature is still on our future roadmap! I will add you to the request and from there we will be able to update you on progress.
0
Hi MacKenzie Schrotenboer, could you please add me in this request as well? I am triyng to use "SUMPRODUCT" function, but it doesn't work.
0
Got you added Danilo Sato!
0
Hi MacKenzie Schrotenboer, I do need the sumproduct function as well, so could you please add me in this request?
I just ran into the same situation as Danilo, so I have to find a workaround for now.
0Accedi per aggiungere un commento.
Commenti
28 commenti