COUNTIFS with a checkbox column

Options

I am working in a smart sheet in which I need 2 criteria met before it gives me a count. First criteria I need to meet is that the cell in the column titled SAM needs to be checked in the checkbox. Second criteria that needs to be met is that the Division Column contains “Courts & Justice”.

So I want a count of all “Courts & Justice” if the SAM column is checked, and then the Division has Courts & Justice listed.

I had been using this formula (which worked), before I had to add the additional criteria of the SAM checkbox being checked.

=COUNTIF(Division:Division, CONTAINS("Courts & Justice", @cell))

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    Hi @christy3210

    Please add an additional condition, "SAM:SAM, 1."

    =COUNTIFS(Division:Division, CONTAINS("Courts & Justice", @cell), SAM:SAM, 1)



  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    Hi, Christy -

    I think you have one of two options. If your [Division] column values are fixed (like a drop-down menu rather than freeform entry), you can use this formula:

    =COUNTIFS(SAM:SAM, 1, Division:Division, "Courts & Justice")

    If your [Division] column values are variable (entered freeform), you might want this instead:

    =COUNTIFS(SAM:SAM, 1, Division:Division, CONTAINS("Courts", @cell))

    I tested both with the data sample below, and they worked (see the values in the Counts column - both returned 3 for the number of rows that have both SAM checked and "Courts & Justice as the division).

    You may have to rename your columns to match those on your sheet.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!