Excel Formula Help !!!

[arvarr]

Hi
Can someone please help with formula?

1. Firstly, I am trying to obtain the unique occurences in Column A and Column B.

I can obtain the occurences in column C as it is a value but for text, it comes up with #VALUE! and for Dates, it comes up with a date. The formula that i used might not be appropriate for this scenario so hopefully, someone can help me out.

I have tried to use Pivot Table to count but it does not seem to count the unique values as it count all non-blanks.

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/21 5
5 David 2010/03/01 6
6 Sam 2010/04/01 7
7 Tom 2010/05/01 2
8 #VALUE! 2451/01/10

Formula in A8 = =SUMPRODUCT(A2:A7/COUNTIF(A2:A7,A2:A7))
Formula in B8 = =SUMPRODUCT(B2:B7/COUNTIF(B2:B7,B2:B7))

Thanks.

 

[GlennUK]

Surely it should be SUMPRODUCT(1/ etc etc ?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[arvarr]

Hi GlennUK
Can you please provide the full formula?
Thanks.

 

[GlennUK]

Like:

=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[arvarr]

Hi GlennUK
That works. Thanks a lot.

If I wanted to look for a particular person as criteria, what would be the best way to incorporate the formula that you have supplied.

I wish to locate the number of unique occurences for Peter in cells B10 and B11.

E.g
Cell A10 = Peter
Cell A11 = David

Thanks.

 

[SkipVought]

8 #VALUE! 2451/01/10

will be a problem!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

I wish to locate the number of unique occurences for Peter in cells B10 and B11.

E.g
Cell A10 = Peter
Cell A11 = David

You example is not clear to me. Care to explain further?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[arvarr]

Hi
Sorry for the confusion.
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/21 5
5 David 2010/03/01 6
6 Sam 2010/04/01 7
7 Tom 2010/05/01 2
8 #VALUE! 2451/01/10

Formula in A8 = =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))
Formula in B8 = =SUMPRODUCT(1/COUNTIF(B2:B7,B2:B7))

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

The formula =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7)) gives me the unique occurences within the range in column A.
But, if I wanted to find unique occurences only for Peter or David (these are the criteria), what formula can be used?

Thanks.

 

[SkipVought]

Use the PivotTable Wizard. You can get a list of unique values

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skup
I have used the Pivot Table but it does not give me the unique count.

Example
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

In pivot table, Row = Nmae, Data = Count of Week Ending Date

For Peter, the count is 2, but it should be 1 as both dates are the same (2010/02/15).

Thanks.

 

[SkipVought]

You said you wanted a unique list of names, so why do you have all that other stuff in your PT?

Drag everything off the PT except Name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
With the unique list of names, I can obtain that via advanced filter.

However, when I obtain the list of unique names, I need a formula that counts the unique occurences of the week ending dates and pivot table does not give me the result that i want as indicated above.

Thanks.

 

[Gavona]

Create a pivot with Rowfields of Name and Date.
Do NOT total for each name.

Let's assume that the first name in your pivot table is in B15
Formula in A15 and copied down beside the whole table:
=IF(ISBLANK(B15),A14,B15)

My result:[tt]
Count of Hours
Name Week Ending Date Total
David       David       15/02/2010 1
David       01/03/2010 1
Peter       Peter       15/02/2010 2
Sam         Sam         01/04/2010 1
Tom         Tom         01/05/2010 1
Grand Total 6[/tt]


Then just Countif on that first column, so in B10:
=COUNTIF($A$15:$A$19,A10)


Gavin