Creating Formula to COUNTIFS two referenced columns from another sheet.

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 Tutwiler
    David Tutwiler Overachievers
    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 Tutwiler
    David Tutwiler Overachievers
    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 Tutwiler
    David Tutwiler Overachievers

    Awesome! Glad it's working.

Help Article Resources