Needing some help on a =countifs formula


We are trying to count how many service calls our techs preform weekly, monthly and yearly. I have tried different formulas can cant seem to get anything to work. I thought if I use the this formula I can change the email address to the techs initial's then count the initial's in a different cell. We want to use this data in a dashboard to show our completed service calls. I am fairly new to the Smartsheet's and finding out the excel formulas don't transfer over all of the time.


Any help would be amazing!

Best Answer


  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Looks pretty good, but I think you have a few inconsistencies. The : is missing from a few of your ASSIGNED TECH calls, and there is no reference to which cell you're looking at. I would expect to see something like ASSIGNED TECH:@row or [ASSIGNED TECH:]@row.

    An easy way to see which one you need to to start writing the formula, and then click on a cell in Assigned Tech and Smartsheet will auto-fill that cell name (ie. [ASSIGNED TECH:]6). Then you can manipulate it how you want.

    Does that make sense?

  • David,

    Thanks for the response. I fixed my original formula =IF(ASSIGNED TECH:="","cm",IF(ASSIGNED TECH:="","ph",IF(ASSIGNED TECH:="","cv",IF(ASSIGNED TECH:="","jz",IF(ASSIGNED TECH:="","jw",IF(ASSIGNED TECH:="","db"))))))

    But still have no luck. So I tried =COUNTIFS([ASSIGNED TECH:]:[ASSIGNED TECH:], FIND("", "cm", "1")) but I am getting an invalid data type. Not sure?

  • Thank you David,

    If i could ask another question? Is there a way to do this in Smartsheets? In excel, under formula i am using =IF(D2="","cm",IF(D2="","ph",IF(D2="","cv",IF(D2="","jz",IF(D2="","jw",IF(D2="","db")))))) to turn an email address into initials. the D2 is the assigned tech column. Then were the techs names are about 10 rows down below formula i made a formula to add the initials up.

    hopefully this makes sense?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!