Checkbox And Text COUNTIF

Hello,

I am looking for some help regarding a formula I am trying to get to work. I am trying to count of all boxes that are unchecked and have a text string that records the area STL. The check marked box represents a finished project and unchecked is unfinished and the text string represents the area of the project. I feel like I am missing something simple. Below is my formula that keeps returning #UNPARSEABLE.

=COUNTIF(Complete1:Complete396, 0) AND(Area1:Area396,="STL")

For a more simple breakdown

=COUNTIF(Checkmarkcell1:CheckmarkCell396, 0) AND(Textstring1:Textstring396,="STL")



Thanks!

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Nicholas Herbstreit

    When you have multiple criteria to consider, you Must use the plural version of Countif. The CountIfs (plural) version can always be used - even if you have a single criteria. Because of this flexibility, my personal preference is to always use the plural version of any formula- regardless if I'm only needing a single criteria evaluated.

    The COUNTIFS function has the syntax =COUNTIFS(range1, criteria1, range2, criteria2, etc etc).

    Unless you specifically need the range of cell1 to cell396 you can reference the entire column by leaving the row numbers out of your formula. This will keep your formula dynamic if your range shrinks or expands.

    =COUNTIFS(Checkmarkcell:Checkmarkcell, 0, Textstring:Textstring,"STL")

    If these aren't your actual column names, please correct formula above to match your column names

    cheers

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Nicholas Herbstreit

    When you have multiple criteria to consider, you Must use the plural version of Countif. The CountIfs (plural) version can always be used - even if you have a single criteria. Because of this flexibility, my personal preference is to always use the plural version of any formula- regardless if I'm only needing a single criteria evaluated.

    The COUNTIFS function has the syntax =COUNTIFS(range1, criteria1, range2, criteria2, etc etc).

    Unless you specifically need the range of cell1 to cell396 you can reference the entire column by leaving the row numbers out of your formula. This will keep your formula dynamic if your range shrinks or expands.

    =COUNTIFS(Checkmarkcell:Checkmarkcell, 0, Textstring:Textstring,"STL")

    If these aren't your actual column names, please correct formula above to match your column names

    cheers

    Kelly

  • I knew it had to be something simple. This worked perfectly. Thanks for the help Kelly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!