Can Someone QA my Formula?
I'm attempting to create a formula the matches the filter conditions that I have on this report:
I want to count the number of rows that meet these criteria (count if (this or this or this or this) AND (that or that))
=COUNTIFS(OR([All Results Expected]:[All Results Expected] <> "", [Corrections Expected]:[Corrections Expected] <> "", [Results Expected]:[Results Expected] <> "", [Update Expected]:[Update Expected] <> "") AND(OR([Record Status]:[Record Status] <> "No Longer Public", [Overall Status CT.gov]:[Overall Status CT.gov] <> "Withdrawn")))
This formula is #UNPARSEABLE.
Can you help me figure out how my translation is failing? (I tried the AI - and it didn't come up with the right answer)
Thank you, Smartsheet Braintrust!
Meredith
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Answers
-
I guess I need to tell it what to count. Hmmmm.
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
I noticed one thing right away you forgot a coma between your first OR statement and your AND statement.
=COUNTIFS(OR([All Results Expected]:[All Results Expected] <> "", [Corrections Expected]:[Corrections Expected] <> "", [Results Expected]:[Results Expected] <> "", [Update Expected]:[Update Expected] <> ""), AND(OR([Record Status]:[Record Status] <> "No Longer Public", [Overall Status CT.gov]:[Overall Status CT.gov] <> "Withdrawn")))
From what I could see there is no other problems.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thanks for you edit @Mark.poole, I've added the comma - this is still getting an UNPARSEABLE error.
I didn't give it directions to count a specific range. Is this what I'm missing? But if this also UNPARSEABLE.
=COUNTIFS([COUNT THIS RANGE]:[COUNT THIS RANGE], (OR([All Results Expected]:[All Results Expected] <> "", [Corrections Expected]:[Corrections Expected] <> "", [Results Expected]:[Results Expected] <> "", [Update Expected]:[Update Expected] <> ""), AND(OR([Record Status]:[Record Status] <> "No Longer Public", [Overall Status CT.gov]:[Overall Status CT.gov] <> "Withdrawn"))))
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
I have also researched it for you a bit. That is correct. OR functions don't really work with countifs. Unless your looking at the same range with different criteria. Instead it looks like you would want. I am still learning the Ins and Outs of the different formulas and what can be achieved with the OR and AND functions.
=Countifs([Count This Range]:[Count This Range],"your looking for",[All Results Expected]:[All Results Expected], <> "")+Countifs([Count This Range]:[Count This Range],"your looking for",[Corrections Expected]:[Corrections Expected], <> "")+….
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thanks for this idea @Mark.poole - I think that might double count rows where multiple criteria are met?
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
It will indeed. I never said the work around was pretty, You would THEN have to go back and subtract from the total. I found this answer on Youtube of all places so I will not take any credit for it. Here is the link to the video I found.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Thanks again @Mark.poole
Helper columns to the rescue. I made a column for each criteria on my sheet with a logic statement in each to return 1 if the cell was not blank. Then I added a fifth column to sum those - and a formula to count that row if it is equal to or greater than 1.
So much easier than attempting smartsheet countifs and or logic.
Thanks for the idea!
MeredithMeredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 287 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!