Get unique value if certain criteria met

[arvarr]

Hi
Can someone please kindly assist?

A B C
1 Name Week Ending Date Hours
2 Peter 2010/02/15 2
3 David 2010/02/15 4
4 Peter 2010/02/15 5
5 David 2010/03/01 6
6 Sam 2010/04/01 7
7 Tom 2010/05/01 2

These are the criteria.
Cell A10 = Peter
Cell A11 = David

The formula in cell B8 =SUMPRODUCT(1/COUNTIF(B2:B7,B2:B7)) gives me the unique occurences within the range in column B.

I need to find the number of unique occurences for the week ending dates for Peter and David.

For Peter, unique count should be 1 and for David, unique count should be 2.
Thanks.

Thank you muchly if anyone can help.

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[arvarr]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Thanks for the info. I have updated the title with correct info. now.

[royUK]

It still contains unnecessary wording - "Formula Help"

[zbor]

arvarr, you can use this for counting unique occurances: =SUMPRODUCT((B2:B7<>"")/COUNTIF(B2:B7, B2:B7&"")).
Your solution will give you error if there is blank value in the range.

Solution for your problem after you adopt title name

[arvarr]

Title changed as per request. Thanks

[arvarr]

arvarr, you can use this for counting unique occurances: =SUMPRODUCT((B2:B7<>"")/COUNTIF(B2:B7, B2:B7&"")).
Your solution will give you error if there is blank value in the range.

Solution for your problem after you adopt title name

Hi zbor
Thanks for your reply.
The formula that you provided - =SUMPRODUCT((B2:B7<>"")/COUNTIF(B2:B7, B2:B7&"")) only provides me the unique occurences for the whole range.

If i have criteria such as if I need to find the number of unique occurences for the week ending dates for Peter and David (see above).

Thanks.

[DonkeyOte]

zbor...The formula that you provided ...only provides me the unique occurences for the whole range.

zbor was simply stating that your existing unique function would fail if you had blanks - it was not posted as an answer to your actual question.

Pre XL2007:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

In addition to above - for XL2007+

Please Login or Register  to view this content.

[arvarr]

Hi DonkeyOte
Thanks for the formula. Works like a charm.

Are you able to give me an explanation of the breakdown of this formula:

=SUMPRODUCT(--($A$2:$A$7=$A10),--(MATCH($A$2:$A$7&"@"&$B$2:$B$7,$A$2:$A$7&"@"&$B$2:$B$7,0)=(ROW($A$2:$A$7)-ROW($A$2)+1)))

Greatly appreciated.
Thanks.

[DonkeyOte]

The first array tests that value in A2:A7 matches the name specified in A10 and returns 1 if True (0 if not)

The second array tests to see if the MATCH of A&B of current iteration against A&B in general equals the row index of the current iteration.
If it does then you know the combination is not a duplicate given the A&B MATCH does not appear in prior rows.
Again where this test holds true the output is 1 (else 0)

The product of the 2 arrays is your resulting unique count for specified name.

[arvarr]

Thanks for the detailed explanation. You're a treasure. Thank you once again.

[DonkeyOte]

No problem - going forward please try to avoid quoting prior posts in their entirety as this just clutters your thread (and board in general).