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

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭✭
    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

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭✭
    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⬆️

  • Amanda MedinaIW
    edited 10/09/23

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

  • Anjanesh Vaidya
    Anjanesh Vaidya ✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!