Count Distinct names, multiple columns, on specific dates

Dominick Vargas

Hi,

I manage a personnel grid where each day I need to staff certain shifts, by position. Ultimately, I need to be able to count how many days each person worked, regardless of what position they did that day.

In the example below, I need to know how many days that John Doe, Jane Doe John Smith and John Jones all worked in total. So I want to count that distinct name, per distinct day. In essence, in the below example, I want to know John Doe worked 3 days, Jane Smith worked 3 days, John Smith worked 2 days and John Jones worked 1 day. I highlighted the names to help identify them for the example.

I'm assuming it's some form of COUNTIFS(DISTINCT(COLLECT)) formula, but I just can't get the right syntax.

Appreciate any help here!

Dominick Vargas

Thank you all for the quick responses! This is an amazing community.

And I think we got it! Instead of a join formula, I did the All name column using an If formula. That way the combo name return looked neat and I could use it in some other ideas.

=IF(ISBLANK(Cashier@row), "", Cashier@row + ", ") + IF(ISBLANK(Restock@row), "", Restock@row + ", ") + IF(ISBLANK(Delivery@row), "", Delivery@row)

Then I used the count formula @Leibel S mentioned. But for the contains part, I did a cell reference at row

=COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, @cell <> "", All:All, CONTAINS(Name@row, @cell))))

And that's it! The counts seem to be pulling in exactly how I need them. This is excellent and a huge time saver. If I run into any hiccups, I'll continue this thread. But really appreciate the help, team.

Paul Newcome

Can you provide a mocked up example of what the output should look like?

AKnight

Hi @Dominick Vargas !

From my understanding you can't use COUNTIFS with DISTINCT, only COUNT; however using COUNT(DISTINCT(COLLECT(Date:Date, Cashier:Cashier, "John Doe", Restock:Restock, "John Doe"))) will also not work because it will see if Cashier and Restock both contain John Doe in that row resulting in 0. I haven't been able to come up with a solution yet, and I am excited to see who does.

For further clarification you will have this formula housed in the Sheet Summary and a distinct formula for each employee, correct?

Leibel S

@Dominick Vargas

My suggestion would be to make a column that combines all 3 fields (cashier restock and delivery).

Lets call it All with a formula of:

=JOIN(Cashier@row:Delivery@row, CHAR(10))

then your count formula would be:

=COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, @cell <> "", All:All, CONTAINS("John Doe", @cell))))

AKnight

Hey @Leibel S !

Why would you need to include the portion ",Date:Date, @cell <>""," ? In the case of the assumption of there being a person mentioned in the row without the date being present?

Leibel S

@AKnight It can actually be removed because count does not count blank cells

Dominick Vargas

Thank you all for the quick responses! This is an amazing community.

And I think we got it! Instead of a join formula, I did the All name column using an If formula. That way the combo name return looked neat and I could use it in some other ideas.

=IF(ISBLANK(Cashier@row), "", Cashier@row + ", ") + IF(ISBLANK(Restock@row), "", Restock@row + ", ") + IF(ISBLANK(Delivery@row), "", Delivery@row)

Then I used the count formula @Leibel S mentioned. But for the contains part, I did a cell reference at row

=COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, @cell <> "", All:All, CONTAINS(Name@row, @cell))))

And that's it! The counts seem to be pulling in exactly how I need them. This is excellent and a huge time saver. If I run into any hiccups, I'll continue this thread. But really appreciate the help, team.