COUNT(DISTINCT(COLLECT - Counting a value per employee

Denisse A

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,

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

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