Percentage with Multiple Criteria
Hello,
I want to see if there is a more efficient formula to do this.
There are multiple order lines and I want to find out what the percentage of shipped items for that order, but it seems like a slow formula. Any thoughts on how I can make this better?
=IFERROR(COUNTIFS([Sales Order #]:[Sales Order #], [Sales Order #]@row, [Shipped Complete]:[Shipped Complete], @cell = "Yes") / COUNTIF([Sales Order #]:[Sales Order #], [Sales Order #]@row), "")
Answers
-
That's pretty much the same way I would do it.
-
Thank you @Paul Newcome . I have a sheet that has 6000 lines and using that formula gives me this error message. It's the only formula I have on the sheet.
-
Unfortunately that limitation cannot be expanded to allow for additional references. You will need to find a way to break down the data whether it be by moving some rows to a different sheet or by grouping entries by (for example) Sales Order #, indenting all of the matching sales orders under the parent row, then leveraging hierarchy functions such as CHILDREN.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!