Count the number of unique items (multiple columns)

**HJHamm** · 03-13-2015, 03:58 PM

I have looked and the only reference I can find is how to count the number of unique items in a single column. The most common solution is "sum(1/Countif(range, range))" using an array scenario. However I actually need to look at three different columns and "weed" out all the duplications across all three at the same time. Is there a way to use Countifs in this scenario? I have tried it 3-4 ways and cannot get it to work. Or, does anyone know of a solution that will work? Thanks!

**Pepe Le Mokko** · 03-13-2015, 04:03 PM

Unique or distinct items please?

**daddylonglegs** · 03-13-2015, 04:04 PM

Do you mean three consecutive columns? If so you could use the type of approach you suggested....or do you have 3 discontiguous columns?

**HJHamm** · 03-13-2015, 04:22 PM

Daddy, yes in 3 consecutive columns I have tried:

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

both fail, the latter with #Div/0!

Pepe, Unique items... if I am understanding you correctly.

**daddylonglegs** · 03-13-2015, 04:56 PM

Many people talk about "counting unique" when they really mean counting distinct items. Taken literally "counting unique" within this dataset:

banana
banana
apple
apple
apple
orange

would give a result of 1 (orange is the only unique item)

but I assume you want to count distinct, in which case the result is 3 for that example

Can you clarify exactly what you want to do in your scenario? - you seem to be using criteria like "*aging" and sheet3!G24

Are you trying to count distinct items but only when other criteria apply?

If you want to count distinct items in column Q where the criteria apply in the other two columns then is is probably the most efficient way to do that:

Formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

**Pepe Le Mokko** · 03-14-2015, 12:23 PM

At last I see someone who agrees with me...

**bebo021999** · 03-14-2015, 12:40 PM

I attach herewith a small samle for extracting unique items from 3 columns

**HJHamm** · 03-20-2015, 10:41 AM

Daddy... that was dead on! I was on another one of the "boss man" agendas and just back to this one... I appreciate the help from each one of you as well