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
-
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")
Answers
-
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?
-
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")
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!