Count Unique Names with Criteria

[shawnw]

Hello,

I'm really struggling with this one. I read a few articles and even watched a video, but it's not working out for me.

• I have a list of team members (e.g., Joe, Mary, Bill) in Col A.
• Each name appears once in the list. No name is repeated.
• For each name, I have a cell with the number of hours that person worked (e.g., 0, 1, 2...infinity).
• I am trying to get a count of the names where the number of hours is greater than zero.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]COL A[/TD]
[TD="align: center"]COL B[/TD]
[/TR]
[TR]
[TD]R1[/TD]
[TD]Name[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]R2[/TD]
[TD]Andy[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]R3[/TD]
[TD]Bill[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]R4[/TD]
[TD]Charlie[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]R5[/TD]
[TD]David[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R6[/TD]
[TD]Edward[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]R7[/TD]
[TD]Frank[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]R8[/TD]
[TD]George[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R9[/TD]
[TD]Harry[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]R10[/TD]
[TD]Ian[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]R11[/TD]
[TD]Jack[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


In the example above, I have 10 unique names. Of these, 3 have zero hours and should be excluded. Seven have hours > 0 and should be counted.

So now in a separate worksheet I need a formula that results in "7" (indicting that 7 unique names had greater than 0 hours).

Thanks!

 

[shawnw]

I think I got it!

=SUM(--(FREQUENCY(IF(People[Total Effort]>0,MATCH(People[VersionOne User],People[VersionOne User],0)),ROW(People[VersionOne User])-ROW(People!A2)+1)>0))

 

[Marcelo Branco]

As there are no duplicates, you can use a simpler formula

=COUNTIF(Sheet1!B2:B11,">0")

M.