Creating Formula to COUNTIFS two referenced columns from another sheet.

09/21/21
Accepted

I'm attempting to create a formula that COUNTIFS two columns from a referenced sheet and am having no luck. Here are the two I've tried:

=COUNTIFS({Start Date}, 0, AND({Not Applicable}, 0)) Error - #Incorrect Argument Set

=COUNTIFS({Start Date}, 0, AND({Not Applicable}, 0)) Ends in a total of 0

The 0 isn't a true result.

References are as follows:

Start Date - a date field, I want to count how many are blank

Not Applicable is a Checkbox field, I want to count how many are not checked

I want the total if both conditions are met.

Best Answer

  • David TutwilerDavid Tutwiler Overachievers
    Accepted Answer

    I think you've actually overcomplicated it, which is good because it is much easier to simplify than to add complexity.

    COUNTIFS in Smartsheet formulas is, by its nature, and AND formula. What you're doing is saying COUNT IF a AND IF b. So you don't need the AND function for the formula at all. I think what you're looking for is:

    =COUNTIFS({Start Date}, ISBLANK(@cell), {Not Applicable}, false)

Answers

  • David TutwilerDavid Tutwiler Overachievers
    Accepted Answer

    I think you've actually overcomplicated it, which is good because it is much easier to simplify than to add complexity.

    COUNTIFS in Smartsheet formulas is, by its nature, and AND formula. What you're doing is saying COUNT IF a AND IF b. So you don't need the AND function for the formula at all. I think what you're looking for is:

    =COUNTIFS({Start Date}, ISBLANK(@cell), {Not Applicable}, false)

  • Works perfectly!

  • David TutwilerDavid Tutwiler Overachievers

    Awesome! Glad it's working.

Sign In or Register to comment.