Subtotal

  • Thread starter Thread starter dolphinv4
  • Start date Start date
D

dolphinv4

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
 
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,
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

.
Click to expand...
 
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
 
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
 
Back
Top