Needing some help on a =countifs formula

Hello,

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.

=IF(ASSIGNED TECH:="cmichaels@mtwysys.com","cm",IF(ASSIGNED TECH:="phuber@mtwysys.com","ph",IF(ASSIGNED TECH:="cvincent@mtwysys.com","cv",IF(ASSIGNED TECH="jzimmerman@mtwysys.com","jz",IF(ASSIGNED TECH:="jweaver@mtwysys.com","jw",IF(ASSIGNED TECH="dbogan@mtwysys.com","db"))))))

Any help would be amazing!

Best Answer

Answers

  • 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:="cmichaels@mtwysys.com","cm",IF(ASSIGNED TECH:="phuber@mtwysys.com","ph",IF(ASSIGNED TECH:="cvincent@mtwysys.com","cv",IF(ASSIGNED TECH:="jzimmerman@mtwysys.com","jz",IF(ASSIGNED TECH:="jweaver@mtwysys.com","jw",IF(ASSIGNED TECH:="dbogan@mtwysys.com","db"))))))

    But still have no luck. So I tried =COUNTIFS([ASSIGNED TECH:]:[ASSIGNED TECH:], FIND("cmichales@mtwysys.com", "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="cmichaels@mtwysys.com","cm",IF(D2="phuber@mtwysys.com","ph",IF(D2="cvincent@mtwysys.com","cv",IF(D2="jzimmerman@mtwysys.com","jz",IF(D2="jweaver@mtwysys.com","jw",IF(D2="dbogan@mtwysys.com","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!