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
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

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
Help Article Resources
Categories
Check out the Formula Handbook template!