Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Counting unique values, 2 columns, alphanumeric

1. I agreed to these rules
Join Date
16th February 2005
Posts
3

## Counting unique values, 2 columns, alphanumeric

I've been all over the map on this one, and really need some help.

I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows. An example:

5CZ975 Van
25RW04 Straight
5CZ975 Van
ZJ6026 Van

So, the result I am after is 2 vans, 1 straight truck

I found an array in a another post, but it is not working properly (see cell F7 in the attached):

=sum(if(frequency(if(\$b\$2:\$b\$18=\$f7,\$a\$2:\$a\$18),\$a\$2:\$a\$18)<>"",1,0))
**ctrl+shift+enter**

Any help/suggestions would be most appreciated! Thanks in advance.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Couting unique values, 2 columns, alpahnumeric

Easier than you might've thought...
Then again, anything is easy if you've done it before right?

=SUMPRODUCT( 1/COUNTIF(\$A\$2:\$A\$18,\$A\$2:\$A\$18), --(\$B\$2:\$B\$18=F7) )

It's a single non-array formula that doesn't need the extra column to do the calc!

3. I agreed to these rules
Join Date
16th February 2005
Posts
3

## Re: Couting unique values, 2 columns, alpahnumeric

There I go again, making a mountain out of a molehill! Actually, your solution is the preferred route as I would like to aviod using an array (that would mean I would have to explain what it does ).

Perfect, thank you so much!

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Couting unique values, 2 columns, alpahnumeric

I edited my original post because I came up with a clean non-array formula solution... you might find it interesting. But... you might have trouble explaining it to the simple folk.

5. I agreed to these rules
Join Date
16th February 2005
Posts
3

## Re: Couting unique values, 2 columns, alpahnumeric

I lost them in the first explanation, which was quickly followed by their response, "hey, as long as it works."

Thanks Aaron!

Excel Video Tutorials / Excel Dashboards Reports

6. Member
Join Date
3rd January 2007
Posts
51

## Re: Couting unique values, 2 columns, alpahnumeric

Thanks guys. I though for sure I would need VBA coding but this solves my problem nice and easy. This is great. Kudos.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno