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

Tags:

Answers

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    @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.

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/06/24

    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.

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    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.

  • Meredith Rhodes
    Meredith Rhodes ✭✭✭✭

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!