View Full Version : Solved: Count unique numbers

IgnBan

02-03-2008, 03:31 PM

I need to count unique (with this I mean not duplicated) numbers in Seet1 A1 from on a range in Sheet2 range A1:A10000, what will be the expression in cell Sheet1 A1? I'm struggling with count's IF, Frecuency, sumproducts' ect and can come with the formula, I know I can use the filter but I need the count to link to chart.

=SUMPRODUCT((Sheet2!A1:A10000<>"")/COUNTIF(Sheet2!A1:A10000,Sheet2!A1:A10000&""))

danlu

02-19-2008, 02:12 AM

Hi

I tried your suggestion and it works fine, though it seems to be tuff for excel because after adding this formula to my sheet everything goes much slower (also when you just try to add a new value to cell in the A column it takes you several seconds for Excel to take you to another cell after hitting the enter button).

Is there some way to slim this formula it order to make it run faster?

danlu

02-19-2008, 02:14 AM

Hi again

Tried with fewer values as my range in the A column which improved the speed a lot. So i guess if you can live with having less than 1000 (or maybe a bit more) values then speed is good.

danlu

02-19-2008, 02:23 AM

Hm

If you want to extend this, that is, to count all unique values in column A combined with a defined value in column B.

For ex if there are alpha values in column A: Say A,B,C,D,E and in column B you have values such as 1,2,3,4 where these values can occur several times in column B but only once (hopefully) for each value in column A. Could look like:

A 1

A 2

A 3

B 1

B 3

C 1

C 2

C 3

Then for ex I want to to be able to define that I want to count the number of times that for ex no. 2 occurs in column B assuming this value only exists once for each letter in column A. (If this is not the case it would be good if the fomula would return some error).

=SUM(--(FREQUENCY(IF(B2:B100=2,MATCH(A2:A100,A2:A100,0)),ROW(INDIRECT("1:"&ROWS(A2:A100))))>0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.Note that you cannot use a whole column in array formulae (prior to excel 2007), but must use an explicit range.

danlu

02-21-2008, 02:35 AM

Hi

Thanks, great it works perfectly!

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.