Needing some help on a =countifs formula

Derek Bogan

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!

David Tutwiler

I don't think you need the find. You should just be able to use the COUNTIFS and the same range you have, but just put the email address in tight next field without the find.

=COUNTIFS([ASSIGNED TECH:]:[ASSIGNED TECH:],"cmichales@mtwysys.com")

David Tutwiler

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?

Derek Bogan

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?

David Tutwiler

I don't think you need the find. You should just be able to use the COUNTIFS and the same range you have, but just put the email address in tight next field without the find.

=COUNTIFS([ASSIGNED TECH:]:[ASSIGNED TECH:],"cmichales@mtwysys.com")

Derek Bogan

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?