Counting physical inventory while removing duplication...

Drew Frazelle
Drew Frazelle ✭✭✭
edited 08/15/22 in Formulas and Functions

I'm trying to count the number of unique pieces of equipment that are on my worksite. Each unit has a specific Unit Number listed in a sheet. Units that are On-Site are indicated by a green dot in another column. The tricky part for me is, some of these Unit Numbers have multiple work orders which are listed on another column, and a total work order count does not represent how full our facility truly is.


I was thinking of using:

=COUNTIFS(DISTINCT([Unit #]:[Unit #]... but how do I complete a DISTINCT count formula while only considering those On-Site "Green" indicated cells???


Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Drew Frazelle

    Hi Drew,

    Try something like this.

    =COUNTIFS([Job #]:[Job #]; <>""; [On-Site / Off-Site]:[On-Site / Off-Site]; "Green")

    The same version but with the below changes for your and others convenience.

    =COUNTIFS([Job #]:[Job #], <>"", [On-Site / Off-Site]:[On-Site / Off-Site], "Green")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Nasir@EBC
    Nasir@EBC ✭✭✭

    Hello Drew

    Can you please phrase your question in the following manner so I can try and help you asap! :)

    "I want to count all the values of Column X which are [specify all your criteria here]"

    regards

    Nasir (Zealvert)

  • Gordon
    Gordon ✭✭✭✭✭

    I'm also interested in this, as I have a similar situation, but with just one criteria.

  • @Nasir@EBC

    I want to count all the values of Column “Job #” which have a green dot (On Site / Off Site column) in their corresponding row.

    *Please include in the formula, if possible, the removal of any duplicate “Job #” data.*

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @Drew Frazelle

    Hi Drew,

    Try something like this.

    =COUNTIFS([Job #]:[Job #]; <>""; [On-Site / Off-Site]:[On-Site / Off-Site]; "Green")

    The same version but with the below changes for your and others convenience.

    =COUNTIFS([Job #]:[Job #], <>"", [On-Site / Off-Site]:[On-Site / Off-Site], "Green")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!