Please help! Need COUNTIFS with DISTINCT

JMT

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!

Christian Graf

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.

Christian Graf

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

JMT

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

Christian Graf

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.

JMT

Thank you so much, Christian! It works perfectly!!