COUNTIF, one cell is text, and one cell is 'check box'

Options
SkiPatrolScott
SkiPatrolScott ✭✭✭✭

@Kelly Moore

I have a summary formula that works to count a cell if there is text in it, works good, can weed out the Data that I don't want to count with other summary formulas. However there is often preplanning or left over data. My solution is to show the [route] completed [done].

=countif([done]:[done],1) works

=countif([route]:[route],is text(@cell)) works

But how do I combine these so the route is only counted if done is checked, so in the pic below, only the routes with the check boxed will be counted.

=IF(DONE@row, Route@row, "CANX") is being used as a helper to feed another sheet but I cant figure out how to view when there is all this happening... maybe I should just use my totals sheet?

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @SkiPatrolScott

    @Nick Korna has got you covered!

    The COUNTIF formula only allows one set of criteria to be used, which is why it wouldn't allow you to combine your two singular formulas. My personal preference is to never use the COUNTIF (singular) because of this criteria restriction and to always use a COUNTIFS (or SUMIFS vs SUMIF) because with these plural functions there are no limitations - you can use them with any number of criteria, from one to however many. But that is a personal preference. 😉

    As always, the community is here whenever you have a question.

    Kelly

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @SkiPatrolScott,

    You can just combine your 2 COUNTIF statements into one with COUNTIFS:

    =COUNTIFS([route]:[route],ISTEXT(@cell),[done]:[done],1)

    Hope this helps - if there is a problem or you've any questions then just ask! 😊

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @SkiPatrolScott

    @Nick Korna has got you covered!

    The COUNTIF formula only allows one set of criteria to be used, which is why it wouldn't allow you to combine your two singular formulas. My personal preference is to never use the COUNTIF (singular) because of this criteria restriction and to always use a COUNTIFS (or SUMIFS vs SUMIF) because with these plural functions there are no limitations - you can use them with any number of criteria, from one to however many. But that is a personal preference. 😉

    As always, the community is here whenever you have a question.

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!