Count the number of names in 3 columns depending on the Customer column
Hello,
Please help me with a formula to count the number of technician names in 3 columns (Lead, Support, LOTO) depending on name in the Customer Column.
I need to see the number of techs assigned to Vestas, and the number techs assigned to EDF. I want to count the entire column though, since there are more than two customers. I understand I may need a different formula for each customer.
For Vestas, total should be 11
For EDF, total should be 6
Thank you!
Best Answer
-
Hi Amanda MedinaIW,
As per the condition, I have used 2 helping columns to achieve desired output which are Vestas count and EDF count as shown in pink formatting.
And used following formula in them-
Vestas count- =IF(Customer@row = "Vestas", COUNTM(Lead@row:LOTO@row), "")
EDF count- =IF(Customer@row = "EDF", COUNTM(Lead@row:LOTO@row), "")
After this, I have used Sheet summary field and there in Assigned to Vestas is used -
=SUM([Vestas count]:[Vestas count])
and in Assigned to EDF-
=SUM([EDF count]:[EDF count])
As I tested, It worked !
I Hope it will solve your problem.
Thank You!
Anjanesh Vaidya
Thanks,
Anjanesh Vaidya
Smartsheet Development, Ignatiuz Software
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️
Answers
-
Hi Amanda MedinaIW,
As per the condition, I have used 2 helping columns to achieve desired output which are Vestas count and EDF count as shown in pink formatting.
And used following formula in them-
Vestas count- =IF(Customer@row = "Vestas", COUNTM(Lead@row:LOTO@row), "")
EDF count- =IF(Customer@row = "EDF", COUNTM(Lead@row:LOTO@row), "")
After this, I have used Sheet summary field and there in Assigned to Vestas is used -
=SUM([Vestas count]:[Vestas count])
and in Assigned to EDF-
=SUM([EDF count]:[EDF count])
As I tested, It worked !
I Hope it will solve your problem.
Thank You!
Anjanesh Vaidya
Thanks,
Anjanesh Vaidya
Smartsheet Development, Ignatiuz Software
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️
-
It worked, Thank you!
It might work better if I did it another way though. If I had these columns:
What would the formula be in the Sheet Summary to count the values in the Techs Assigned Column if the value in the Customer column were "Vestas" etc.?
-
Hi Amanda MedinaIW,
To count the values in the Techs Assigned Column, you again have to add another helping column in sheet same as Vestas count as I mentioned and then create sheet summary and use that helping column in Sheet summary field to SUM.
Thank You!
Anjanesh Vaidya
Thanks,
Anjanesh Vaidya
Smartsheet Development, Ignatiuz Software
Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!