Count Check Box with added Sum Formula

=COUNTIF(Route:Route, ISTEXT(@cell)) - [Non Route Entries]#

My current machine will not allow a PIC to be included. The above formula works, but I need to subtract certain entries that are not "routes", hence the [Non Route Entries]#

This is counting the number of routes patrollers go on during an avalanche cycle. However often the route sheet is pre-populated or a route does not get skied and remains on the sheet. My solution is to add if checked to the [Done] row. I have messed with a bunch of different formulas but no love.....

Answers

  • Ipshita
    Ipshita ✭✭✭✭✭✭

    Hi @SkiPatrolScott

    I can suggest an easy workaround for what you are trying to achieve. Using the COUNTIFS formula above, create summary field formulas for the "total number of entries" and another one for just "non route" entries separately in this working sheet of yours and then just add another summary field for "Total" - "non route" - that's your end result. You can even create a column for this summary field in the same working sheet - so say your summary field name is "Route Entries", so in the column use, =[Route Entries]# and it will automatically pick up the value from your summary field.

    Hope this helps.

    Cheers!

    Ipshita

    Ipshita Mukherjee

  • SkiPatrolScott
    SkiPatrolScott ✭✭✭✭

    hey @Ipshita

    That is what I am essentially doing right now. My dilemma is that we sometimes pre-plan several days in advance. The summary sheet is counting routes and then subtracting non route entires with the formula below. So I need to add a count if completed to the count if. my rows are [route] and [done], [done] is a check box... hopefully when I get to my personal machine I can screen shot....

    =COUNTIF(Route:Route, ISTEXT(@cell)) - [Non Route Entries]# how do I add [done] to the countif?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!