Subtotal
 

Hi,

I have a list of records as follows

Apple
orange
grape
apple
apple
grape
watermelon

I want to count the number of unique item above...ie, the
end result should show 4 (apple,orange,grape,watermelon).

Is there a formula to do this?

Thanks!
Val

Hi Val,

I suggest you look he

http://www.cpearson.com/excel/duplic...CountingUnique

for a solution by Chip Pearson

tim

dolphinv4 wrote:
Hi,

I have a list of records as follows

Apple
orange
grape
apple
apple
grape
watermelon

I want to count the number of unique item above...ie, the
end result should show 4 (apple,orange,grape,watermelon).

Is there a formula to do this?

Thanks!
Val

Hi,
I went to look at the page but it does not seem to solve
my problem. I tried to use the example stated, but the
ans to the formula to the given table is 1 instead of 3!

I downloaded the example excel and also, the 4th example
should show 6 but instead, it shows 1..

help! i don't know how to change the formula so that it
shows 6 instead of 1...

val

-----Original Message-----
Hi Val,

I suggest you look he

http://www.cpearson.com/excel/duplic...CountingUnique

for a solution by Chip Pearson

tim

dolphinv4 wrote:
Hi,

I have a list of records as follows

Apple
orange
grape
apple
apple
grape
watermelon

I want to count the number of unique item above...ie,
the
end result should show 4
(apple,orange,grape,watermelon).

Is there a formula to do this?

Thanks!
Val
.

HI,

i tried the formula given in the page give, but the
answer gives a 1 instead of a 3 for the example. Was
there a typo error somewhere?

Also, I downloaded the excel sheet example given in the
webpage, the "Unique entries in UBRng (contains blanks)"
gives an answer 1 instead of 6. How do I change the
formula to get it to show 6 instead?

Val
-----Original Message-----
Hi Val,

I suggest you look he

http://www.cpearson.com/excel/duplic...CountingUnique

for a solution by Chip Pearson

tim

dolphinv4 wrote:
Hi,

I have a list of records as follows

Apple
orange
grape
apple
apple
grape
watermelon

I want to count the number of unique item above...ie,
the
end result should show 4
(apple,orange,grape,watermelon).

Is there a formula to do this?

Thanks!
Val
.

-----Original Message-----
Hi,
I went to look at the page but it does not seem to solve
my problem. I tried to use the example stated, but the
ans to the formula to the given table is 1 instead of 3!

I downloaded the example excel and also, the 4th example
should show 6 but instead, it shows 1..

help! i don't know how to change the formula so that it
shows 6 instead of 1...

val

-----Original Message-----
Hi Val,

I suggest you look he

http://www.cpearson.com/excel/duplic...#CountingUniqu
e

for a solution by Chip Pearson

tim

dolphinv4 wrote:
Hi,

I have a list of records as follows

Apple
orange
grape
apple
apple
grape
watermelon

I want to count the number of unique item above...ie,
the
end result should show 4
(apple,orange,grape,watermelon).

Is there a formula to do this?

Thanks!
Val
.

.

Hi,

i used the formula

=SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH
(Range1,Range1,0),""),
IF(LEN(Range1)0,MATCH(Range1,Range1,0),""))0,1))

and it worked! thanks...however, i need somehow change
the fomula such that it gives the number of unique
entries in range A if the equivalent value in B is
not "XI"....ie, in the example below, the answer I want
should be 3 (Alice and betty....alice should still be
counted...Candy shld be eliminated coz Candy appears
twice but twice the RangeB value is XI)

Is this possible?


RangeA RangeB
alice OK
Betty OK
Candy XI
Betty OK
alice XI
Denise OK
Candy XI

Thanks!
Val
-----Original Message-----
Hi Val,

I suggest you look he

http://www.cpearson.com/excel/duplic...CountingUnique

for a solution by Chip Pearson

tim

dolphinv4 wrote:
Hi,

I have a list of records as follows

Apple
orange
grape
apple
apple
grape
watermelon

I want to count the number of unique item above...ie,
the
end result should show 4
(apple,orange,grape,watermelon).

Is there a formula to do this?

Thanks!
Val
.

Did you enter the formulas as array formulas? After you type the
formula, you must press Ctrl+Shift+Enter rather than just Enter.
If you do this properly, Excel will display the formula enclosed
in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


wrote in message
...
Hi,
I went to look at the page but it does not seem to solve
my problem. I tried to use the example stated, but the
ans to the formula to the given table is 1 instead of 3!

I downloaded the example excel and also, the 4th example
should show 6 but instead, it shows 1..

help! i don't know how to change the formula so that it
shows 6 instead of 1...

val

-----Original Message-----
Hi Val,

I suggest you look he

http://www.cpearson.com/excel/duplic...CountingUnique

for a solution by Chip Pearson

tim

dolphinv4 wrote:
Hi,

I have a list of records as follows

Apple
orange
grape
apple
apple
grape
watermelon

I want to count the number of unique item above...ie,
the
end result should show 4
(apple,orange,grape,watermelon).

Is there a formula to do this?

Thanks!
Val
.

did you try DCOUNT?

"dolphinv4" wrote:

Hi,

I have a list of records as follows

Apple
orange
grape
apple
apple
grape
watermelon

I want to count the number of unique item above...ie, the
end result should show 4 (apple,orange,grape,watermelon).

Is there a formula to do this?

Thanks!
Val