Using countif but not including duplicates

[thedrumtutor]

Hi Everyone, First time posting and an excel novice here. I have entered the following formula to add up a list of data. =COUNTIF(B7:B100,"*") This doesn't include gaps with blank cells and only gives me the total number of cells that contain text. However, some of the text is duplicated and I only want to count the total number of unique entries in the list. Any advice would be much appreciated. Cheers, Chris

 

[ChrisR]

Hi,

Try this.......... Took me a while to figure out because of the blank cells, but hopefully this will be what you're looking for. Please note that this formula must be entered with Ctrl+Shift+Enter or it will not work! If done correctly, you will see { } brackets appear automatically around the formula.

=SUM(IF(FREQUENCY(IFERROR(MATCH(B9:B19,B9:B19,0),0),IFERROR(MATCH(B9:B19,B9:B19,0),0))>0,1,0))-1

Just change the range from B9:B19 to whatever your list range is.........

Cheers
Chris

 

[Oeldere]

Use a helpcolumn with the formula: countif($B$2:$b2,$b2) and drag down. If you get the value 2 or more you have duplicated values. If your using pivot table you can select this item to in- or exclude this values.

 

[ChrisR]

Hi,

Just had a think and you could also use this.........Which is a bit shorter (Still needs Ctrl + Shift + Enter for it to work)

=SUM(IFERROR(1/COUNTIF(A10:A16,A10:A16),0))

 

[kevatarvind]

also below one can try ( use Ctrl + Shift + Enter )


=SUM(IF(FREQUENCY(IF(LEN(B7:B100)>0,MATCH(B7:B100,B7:B100,0),""),IF(LEN(B7:B100)>0,MATCH(B7:B100,B7:B100,0),""))>0,1))

 

[thedrumtutor]

Hi,

Just had a think and you could also use this.........Which is a bit shorter (Still needs Ctrl + Shift + Enter for it to work)

=SUM(IFERROR(1/COUNTIF(A10:A16,A10:A16),0))

Cheers Chris, this worked a treat!!!

 

[Aladin Akyurek]

Hi Everyone, First time posting and an excel novice here. I have entered the following formula to add up a list of data. =COUNTIF(B7:B100,"*") This doesn't include gaps with blank cells and only gives me the total number of cells that contain text. However, some of the text is duplicated and I only want to count the total number of unique entries in the list. Any advice would be much appreciated. Cheers, Chris

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B7:B100<>"",MATCH("~"&B7:B100,B7:B100&"",0)),ROW(B7:B100)-ROW(B7)+1),1))

is faster and not affected by the presence of blank cells, etc.

If there are no special meaning chars around the entries, the "~"& and &"" bits can be removed.