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.

	COL A	COL B
R1	Name	Hours
R2	Andy	8
R3	Bill	6
R4	Charlie	2
R5	David	0
R6	Edward	10
R7	Frank	4
R8	George	0
R9	Harry	9
R10	Ian	12
R11	Jack	0

<tbody>
</tbody>

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.