Looking to COUNTIF with an OR function for a cross sheet formula.

Melitta
Melitta ✭✭✭✭✭
in Formulas and Functions

Having trouble with the last part - the Status column is a dropdown with 5 options. I want it to count if it meets all of the entity, type of request and then if the Status is Ready for Approval OR On Hold. The problem with my formula is after the OR part. Anyone have some advice?

=COUNTIFS({Entity}, [A]109, {Type of Request}, Backfill, {Review}, "0", {Status}, OR(@cell=“Ready for Approval”, @cell=“On Hold”)

Tags:
· Share on FacebookShare on Twitter

Answers

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

    I am not sure if its a copy and paste issue or not. How ever I do notice the formula you provided has a sentax issue. Missing the ) to close off the countif.

    =COUNTIFS({Entity}, [A]109, {Type of Request}, Backfill, {Review}, "0", {Status}, OR(@cell=“Ready for Approval”, @cell=“On Hold”))

    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
  • Melitta
    Melitta ✭✭✭✭✭

    @Mark.poole Thanks. Unfortunately, that's not the issue.

    · Share on FacebookShare on Twitter
  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Try:

    =COUNTIFS({Entity}, [A]109, {Type of Request}, "Backfill", {Review}, 0, {Status}, OR(@cell =“Ready for Approval”, @cell =“On Hold”))

    Is A109 a cell reference or an entity #? If its not a specific cell reference, I'd wrap that in quotes as well.

    =COUNTIFS({Entity}, "[A]109", {Type of Request}, "Backfill", {Review}, 0, {Status}, OR(@cell =“Ready for Approval”, @cell =“On Hold”))

    · Share on FacebookShare on Twitter
  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/439034#Comment_439034

    I usually catch the issues with text not being wrapped in "" and numbers being wrapped in "".

    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

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!