Counting unique values in list of 200 000 rows

**kingoftheace** · 10-16-2012, 02:18 AM

Hello!

I've a situation that I need to count unique values in collumn D based on criterias on A,B,C and O .

I have made a formula that kinda works. Just the problem is that the formula is so heavy that it freezes my computer every time for like 5 minutes (eventhought I have 8 core i7) and that's just one formula whereas I need 24 of those (2 years)

=SUM(--(FREQUENCY(IF(('Data'!$A$2:$A$200000=D$3)*('Data'!$B$2:$B$200000=D$5)*('AData'!$C$2:$C$200000>=D$6)*('Data'!$C$2:$C$200000<=D$7)*'Data'!$O$2:$O$200000="CE");MATCH('Data'!$D$2:$D$200000;'Data'!$D$2:$D$200000;0));ROW(INDIRECT("1:"&ROWS('Data'!$D$2:$D$200000))))>0))

Is there any way to make the formula lighter. Example how to add extra collum to the data sheet where it displays only unique values from collumn D. Then it would be easy to just use basic Countifs formula.

Thanks in advance.

**Sindhus** · 10-16-2012, 02:25 AM

Could you attach a dummy workbook?

**kingoftheace** · 10-16-2012, 03:12 AM

There you go. I needed cut down the rows to only 4000 because of the size limitations here and also few collumn, so the O collumn is now I collumn.

**Sindhus** · 10-16-2012, 03:50 AM

You are trying to only count the no. of unique values rite? Then you can try

Formula:

Please Login or Register  to view this content.

If you tell the criteria in A, B and O column, i can expand the formula further.

**kingoftheace** · 10-16-2012, 04:23 AM

Here are the criterias that need to be met:

here are the criterias:

Sheet 1 Collumn A : equal to Sheet 2 Row 3
Sheet 1 Collumn B : greater than or equal to Sheet 2 Row 10
Sheet 1 Collumn C : greater than or equal to Sheet 2 Row 6 AND smaller or equal to Row 7
Sheet 1 Collumn I : equal to "CE"
Sheet 1 Collumn D : Unique values

So basically what it does is to count number of unique values per month and since there is no month value included in the data, it needs to be constructed from year, week and day numbers.

and I don't quite get how to add those criterias to your formula.

**Sindhus** · 10-16-2012, 04:48 AM

Check if this formula works

Formula:

Please Login or Register  to view this content.

**kingoftheace** · 10-16-2012, 06:12 AM

The formula you gave looks very good and I can't find any mistakes in it myself. It seems to meet all the criterias, but still the result it gives is wrong :/ Something weird in it since it doesnt give always whole numbers, but instead example 2,353 . Count should be always whole numbers, eather it meets the criterias and counts it in as 1, or then it doesn't and gives 0.

Could u tell me how does the 1/countif() work?