Counting Unique Occurences in excel?

**ayo** · 05-13-2008, 04:38 PM

Does anyone here know how to count unique Occurneces in Excel?

For example: -

What formula can I use to count the Unique Occurences non - zero values in Column 2 for the letters in Column 1?

Column 1 Column 2
A 1
A 1
A 1
B 0
C 2
C 2
D -1

**mikerickson** · 05-13-2008, 04:45 PM

What results do you want from the sample data posted?

Do you want the duplicated A 1 's to be counted as one or as none?

**davesexcel** · 05-13-2008, 04:52 PM

Originally Posted by ayo

Does anyone here know how to count unique Occurneces in Excel?

For example: -

What formula can I use to count the Unique Occurences non - zero values in Column 2 for the letters in Column 1?

Column 1 Column 2
A 1
A 1
A 1
B 0
C 2
C 2
D -1

This kind of formula could work for you
=SUMPRODUCT((A1:A7="A")*(B1:B7<>0))

more on sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

**Domenic** · 05-13-2008, 08:33 PM

To count the unique items in the first column, where the corresponding value in the second column is a non-zero value, assuming that A2:B8 contains the data, try...

=SUM(IF(FREQUENCY(IF($A$2:$A$8<>"",IF($B$2:$B$8<>0,MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0))),ROW($A$2:$A$8)-ROW($A$2)+1)>0,1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

**ayo** · 05-14-2008, 11:41 AM

I want duplicate values in the first column to be counted as one. Becos they would correspond to the same non zero value in the second column.\

So A is counted as one since it corresponds to the same non zero value. D is also counted as one since it corresponds to -1, the same for C 2. The formula is going to be used for a larger set.

Thanks - Ayo

A 1
A 1
A 1
B 0
C 2
C 2
D -1

**Domenic** · 05-14-2008, 11:53 AM

Post deleted...

**ayo** · 05-14-2008, 11:59 AM

A 1
A 1
A 1
B 0
C 2
C 2
D -1

Count = 3 ; B is the only field that matches zero

- Ayo

**Domenic** · 05-14-2008, 12:04 PM

The formula I offered should return the desired result. Have you tried it?

**ayo** · 05-14-2008, 12:20 PM

Domenic,

using this: -

=SUM(IF(FREQUENCY(IF($C$3:$C$47164<>"",IF($M$3:$M$47164<>0,MATCH("~"&$C$3:$C$47164,$C$3:$C$47164&"",0))),ROW($C$3:$C$47164)-ROW($C$3)+1)>0,1))

I keep getting this when I past it in a field - #VALUE!

Please Advise

**Domenic** · 05-14-2008, 12:26 PM

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly brackets {....} around the formula. If you've pasted the formula into a cell, click in the formula bar and then confirm with CONTROL+SHIFT+ENTER.