COUNT(DISTINCT(COLLECT - Counting a value per employee

Hi,

I'm trying to count how many values I have for certain people. I have two values that are assigned to the employees daily, for each day we have a value of either "Y" or "N". I want to count how many "Y" an employee has.

I'm getting this information from a big database, to have it summarized in a small one. I've tried a lot of variations but the values are not accurate. So far I've tried:

=COUNT(DISTINCT(COLLECT({Check In (Y/N)}, {Employee Name}, [Employee Name]@row)))

=COUNT(DISTINCT(COLLECT({Employee Name}, {Check In (Y/N)}, "Y")))

=COUNT(DISTINCT(COLLECT({Employee Name}, {Check In (Y/N)}, "Y", {Employee Name}, [Employee Name]@row)))

=COUNT(DISTINCT(COLLECT({Employee Name}, {Check In (Y/N)}, ="Y", {Employee Name} = [Employee Name]@row)))

=COUNT(DISTINCT(COLLECT({Check In (Y/N)}, {Employee Name}, [Employee Name]@row, {Check In (Y/N)}, ="Y")))

Thank you,

Find more posts tagged with

Construction

Formulas

Reports

functionality

Project Management

Accepted answers

Paul Newcome

You should be able to use just a standard COUNTIFS for this one...

=COUNTIFS({Employee Name}, @cell = [Employee Name]@row, {Check In (Y/N)}, @cell = "Y")

All comments

J Tech

Hi Denisse,

Please try the below:

=COUNTIFS({Employee Name}, INDEX({Employee Name}, @row), {Check In (Y/N)}, "Y")

Denisse A

Hello,

It shows invalid. I've tried:

=COUNTIF({Employee Name}, INDEX([Employee Name]@row, {Check In (Y/N)}, "Y"))

=COUNTIF({Employee Name}, INDEX({Employee Name}, [Employee Name]@row, {Check In (Y/N),"Y"}))

=COUNTIFS({Employee Name}, INDEX({Employee Name}, [Employee Name]@row), {Check In (Y/N)}, "Y")

=COUNTIFS({Employee Name}, INDEX([Employee Name]@row), {Check In (Y/N)}, "Y")

I ended up using: =COUNTIFS({Employee Name}, [Employee Name]@row, {Check In (Y/N)}, "Y") and looks like is working.

Thank you,

Paul Newcome

You should be able to use just a standard COUNTIFS for this one...

=COUNTIFS({Employee Name}, @cell = [Employee Name]@row, {Check In (Y/N)}, @cell = "Y")

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.