Please help! Need COUNTIFS with DISTINCT
Hello Community!
I could really use some help. I've reviewed prior posts but am still not nailing it.
I need to count the total number of "to be hired" positions based on unique/distinct ID and team. I've tried so many variations but cannot get it. Any help would be tremendously appreciated.
Thank you!
Best Answer
-
Okay,
Lets make a new sheet called "Team Employee Type/Count". In the new sheet we will make a column that is called "Team". We will now populate that column with all the teams we have, "Content", "Sales", "Analytics", etc. Next we create a "New - To be Hired" column, an "Employee" column, and a "Contractor" column, etc.
In the "New - To be Hired" column, we can use the formula:
=COUNT(DISTINCT(COLLECT({Team}, {Team}, =Team@row, {Type Employee or Contractor}, ="New - to be Hired")))
where {Team} is a cross sheet reference to the original sheet "Team" column, and {Type Employee or Contractor} is a cross sheet reference to the original sheet "Type Employee or Contractor" column.
In the "Employee" column, we can use the formula:
=COUNT(DISTINCT(COLLECT({Team}, {Team}, =Team@row, {Type Employee or Contractor}, ="Employee")))
In the "Contractor" column, we can use the formula:
=COUNT(DISTINCT(COLLECT({Team}, {Team}, =Team@row, {Type Employee or Contractor}, ="Contractor")))
Hope this helps.
Answers
-
You need to provide more information than that. What is each column name? Are they all on the same sheet? Can you share an image of dummy data, or blank out names?
Vague questions get vague answers.
Here is a stab in the dark,
=COUNTIFS([TBH]:[TBH], ="to be hired", [Unique ID]:[Unique ID], =123, [Team]:[Team], ="Team1")
-
Thank you for your feedback! Here is more info:
I need to provide a count of the distinct number of employees for each team and type (employee, contractor or To be Hired). In excel, it's a simple pivot table. Not so easy in SS.
Here's a sample:
As you can see, some employees are listed more than once as we are recording each initiative they are responsible for and the their time allocated to each initiative. For example, I need a formula that will say: the content team has one employee that will be hired and the sales team has 1 employee while analytics has one employee and one that will be hired. Quality has 1 contractor.
Thank you!!!
-
Okay,
Lets make a new sheet called "Team Employee Type/Count". In the new sheet we will make a column that is called "Team". We will now populate that column with all the teams we have, "Content", "Sales", "Analytics", etc. Next we create a "New - To be Hired" column, an "Employee" column, and a "Contractor" column, etc.
In the "New - To be Hired" column, we can use the formula:
=COUNT(DISTINCT(COLLECT({Team}, {Team}, =Team@row, {Type Employee or Contractor}, ="New - to be Hired")))
where {Team} is a cross sheet reference to the original sheet "Team" column, and {Type Employee or Contractor} is a cross sheet reference to the original sheet "Type Employee or Contractor" column.
In the "Employee" column, we can use the formula:
=COUNT(DISTINCT(COLLECT({Team}, {Team}, =Team@row, {Type Employee or Contractor}, ="Employee")))
In the "Contractor" column, we can use the formula:
=COUNT(DISTINCT(COLLECT({Team}, {Team}, =Team@row, {Type Employee or Contractor}, ="Contractor")))
Hope this helps.
-
Thank you so much, Christian! It works perfectly!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!