Counting physical inventory while removing duplication...

Drew Frazelle

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

Andrée Starå

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

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

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

Drew Frazelle

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

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