Count Distinct names, multiple columns, on specific dates

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!

Find more posts tagged with

Accepted answers

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.

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.