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.

    COUNTIFS formula troubleshooting - multiple criteria and checkboxes

    ✭✭
    in Formulas and Functions

    Hi SmartSheets Community,

    I'm new to Smartsheet (but know some basic Excel), but am getting stumped on this formula. Can you advise what I'm doing wrong?

    I'm trying to create a formula that 1) counts either Red or Blue cells in another spreadsheet column (Other Sheet Range 1), and then 2) counts a checkmark (or no check mark). When I enter this in, I get #UNPARSEABLE. Any guidance on what I'm doing wrong?


    Checkmark formula:

    =COUNTIFS({Other Sheet Range 1}, OR(@cell = "Red", @cell = "Blue"), {Other Sheet Range 5}, =1))


    No Checkmark formula:

    =COUNTIFS({Other Sheet Range 1}, OR(@cell = "Red", @cell = "Blue"), {Other Sheet Range 5}, 0))

    Tags:
    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭✭✭✭✭
      Answer ✓

      Try the below: (You had an extra parenthesis at the end in both the formula and also changed =1 to just 1)

      =COUNTIFS({Other Sheet Range 1}, OR(@cell = "Red", @cell = "Blue"), {Other Sheet Range 5}, 1)

=COUNTIFS({Other Sheet Range 1}, OR(@cell = "Red", @cell = "Blue"), {Other Sheet Range 5}, 0)
      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭
      Answer ✓

      Try the below: (You had an extra parenthesis at the end in both the formula and also changed =1 to just 1)

      =COUNTIFS({Other Sheet Range 1}, OR(@cell = "Red", @cell = "Blue"), {Other Sheet Range 5}, 1)

=COUNTIFS({Other Sheet Range 1}, OR(@cell = "Red", @cell = "Blue"), {Other Sheet Range 5}, 0)
      · Share on FacebookShare on Twitter
    • ✭✭
      Sameer Karkhanis

      Thank you Sameer!! This was driving me crazy, and I couldn't figure it out. Appreciate it!!

      · 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