What is wrong with my formula?

Probably something obvious...

=COUNTIF((Position2:Position136, (CONTAINS("Medical Assistant", @cell))), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell))))

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/13/21 Answer ✓

    Original Formula:


    =COUNTIF((Position2:Position136, (CONTAINS("Medical Assistant", @cell))), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell))))

    You went a little crazy on the parenthesis is the first thing I spot

    =COUNTIF(Position2:Position136, CONTAINS("Medical Assistant", @cell), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell

    You don't need the and statement. You need a countifs to put multiple criteria into this.

    =countifs(position2:position136,contains("Medical Assistant",@cell),[cleared to return?]2:[cleared to return?]136,or(contains("No",@cell),contains("Contingent",@cell

    and that should work I think

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/13/21 Answer ✓

    Original Formula:


    =COUNTIF((Position2:Position136, (CONTAINS("Medical Assistant", @cell))), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell))))

    You went a little crazy on the parenthesis is the first thing I spot

    =COUNTIF(Position2:Position136, CONTAINS("Medical Assistant", @cell), AND([Cleared to Return?]2:[Cleared to Return?]136, OR(CONTAINS("No", @cell), CONTAINS("Contingent", @cell

    You don't need the and statement. You need a countifs to put multiple criteria into this.

    =countifs(position2:position136,contains("Medical Assistant",@cell),[cleared to return?]2:[cleared to return?]136,or(contains("No",@cell),contains("Contingent",@cell

    and that should work I think

  • Awesome that worked, thank you!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    It looks like you might be trying to do more than one criteria in COUNTIF. Have you tried constructing this as a COUNTIFS?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!