Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Can Someone QA my Formula?

    ✭✭✭✭✭
    in Formulas and Functions

    I'm attempting to create a formula the matches the filter conditions that I have on this report:

    filter.PNG

    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

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    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

      · Share on FacebookShare on Twitter
    • Community Champion

      @Meredith Rhodes

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 06/06/24
      Meredith Rhodes

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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

      · Share on FacebookShare on Twitter
    • Community Champion

      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.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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!
      Meredith

      Meredith Rhodes, PhD

      ClinicalTrials.gov Specialist

      UW School of Medicine & Public Health

      UW Clinical Trials Institute

      mkrhodes@clinicaltrials.wisc.edu

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions