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,
Best Answer
-
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")
Answers
-
Hi Denisse,
Please try the below:
=COUNTIFS({Employee Name}, INDEX({Employee Name}, @row), {Check In (Y/N)}, "Y")
Regards
J Tech
If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
-
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,
-
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")
