COUNTIFS X or Y or Z (Check boxes)

Hi can I get assistance in writing a formula for the following:

=COUNTIFS({CSN}, ([NOV 5]:[NOV 5], or 1, [NOV 6]:[NOV 6], 1 ,or [NOV 7]:[NOV 7], 1)

Best Answer

  • mcarlson
    mcarlson ✭✭✭✭
    Answer ✓

    To count entries in Smartsheet based on multiple columns with checkboxes, where you want to count if any of the specified dates (e.g., Nov 5, Nov 6, Nov 7) have a checkbox marked, you can use a combination of COUNTIFS and OR logic within a formula.

    Since Smartsheet doesn’t support OR directly inside COUNTIFS, you’ll need to sum multiple COUNTIFS conditions separately and add them together. Here’s how you could set this up:

    Formula

    excelCopy code=COUNTIFS({CSN}, 1, [NOV 5]:[NOV 5], 1) + COUNTIFS({CSN}, 1, [NOV 6]:[NOV 6], 1) + COUNTIFS({CSN}, 1, [NOV 7]:[NOV 7], 1)
    

    Explanation

    • Each COUNTIFS counts rows where {CSN} is 1 and where each specified date column (Nov 5, Nov 6, or Nov 7) is also marked as 1 (checked).
    • Adding COUNTIFS results together simulates an OR condition across the Nov 5, Nov 6, and Nov 7 columns.

    This formula will give you the total count of rows where {CSN} is 1, and at least one of the specified date checkboxes (Nov 5, Nov 6, or Nov 7) is checked.

    Let me know if you need any more assistance!

    Murphy Carlson

    DigitalRadius, Smartsheet Platinum Partner

    Schedule a Meeting

    mcarlson@digitalradius.com

Answers

  • mcarlson
    mcarlson ✭✭✭✭
    Answer ✓

    To count entries in Smartsheet based on multiple columns with checkboxes, where you want to count if any of the specified dates (e.g., Nov 5, Nov 6, Nov 7) have a checkbox marked, you can use a combination of COUNTIFS and OR logic within a formula.

    Since Smartsheet doesn’t support OR directly inside COUNTIFS, you’ll need to sum multiple COUNTIFS conditions separately and add them together. Here’s how you could set this up:

    Formula

    excelCopy code=COUNTIFS({CSN}, 1, [NOV 5]:[NOV 5], 1) + COUNTIFS({CSN}, 1, [NOV 6]:[NOV 6], 1) + COUNTIFS({CSN}, 1, [NOV 7]:[NOV 7], 1)
    

    Explanation

    • Each COUNTIFS counts rows where {CSN} is 1 and where each specified date column (Nov 5, Nov 6, or Nov 7) is also marked as 1 (checked).
    • Adding COUNTIFS results together simulates an OR condition across the Nov 5, Nov 6, and Nov 7 columns.

    This formula will give you the total count of rows where {CSN} is 1, and at least one of the specified date checkboxes (Nov 5, Nov 6, or Nov 7) is checked.

    Let me know if you need any more assistance!

    Murphy Carlson

    DigitalRadius, Smartsheet Platinum Partner

    Schedule a Meeting

    mcarlson@digitalradius.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!