CountIF Formula with Criteria

I have a formula to count how many stores an owner has in our system. It is working great:

=COUNTIF([Primary Contact (Company) (Company)]:[Primary Contact (Company) (Company)], [Primary Contact (Company) (Company)]@row)

(You can see John Voskamp has 91 stores).

I need to determine of the store count, how many are "Open". This is proving more difficult.

I've tried MANY variations on this formula and can't seem to get it to work:

=COUNTIFS([Development Status from SS]:[Development Status from SS], "Open", ([Primary Contact (Company) (Company)]:[Primary Contact (Company) (Company)]))

Any assistance is appreciated!

Best Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    =COUNTIFS([Primary Contact (Company) (Company)]:[Primary Contact (Company) (Company)], [Primary Contact (Company) (Company)]@row, [Development Status from SS]:[Development Status from SS], "Open")

  • Jgorsich
    Jgorsich ✭✭✭✭
    Answer ✓

    You left out your ", [Primary Contact (Company) (Company)]@row" bit, giving no criteria for your second range and making the formula incomplete.

    Take your original formula (just the countif), make it a "countifs" instead of a "countif" - it should still work just fine. Before the closing parenthesis, add your new range and criteria - like this:

    =COUNTIFs([Primary Contact (Company) (Company)]:[Primary Contact (Company) (Company)], [Primary Contact (Company) (Company)]@row,[Development Status from SS]:[Development Status from SS], "Open")

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    =COUNTIFS([Primary Contact (Company) (Company)]:[Primary Contact (Company) (Company)], [Primary Contact (Company) (Company)]@row, [Development Status from SS]:[Development Status from SS], "Open")

  • Jgorsich
    Jgorsich ✭✭✭✭
    Answer ✓

    You left out your ", [Primary Contact (Company) (Company)]@row" bit, giving no criteria for your second range and making the formula incomplete.

    Take your original formula (just the countif), make it a "countifs" instead of a "countif" - it should still work just fine. Before the closing parenthesis, add your new range and criteria - like this:

    =COUNTIFs([Primary Contact (Company) (Company)]:[Primary Contact (Company) (Company)], [Primary Contact (Company) (Company)]@row,[Development Status from SS]:[Development Status from SS], "Open")

  • KCam
    KCam ✭✭✭

    THANK YOU! worked perfect .

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!