# Thread: Count unique records based on criteria of 2nd colu (Excel(2000))

1. ## Count unique records based on criteria of 2nd colu (Excel(2000))

I am looking for a formula that will count the number of unique values based on the value of a second column.
For example: column 1 is the house number, column 2 is the type of pet.
<table border=1><td>House</td><td>Pet Type</td><td>1</td><td>dog</td><td>1</td><td>dog</td><td>1</td><td>cat</td><td>2</td><td>dog</td><td>3</td><td>dog</td><td>3</td><td>cat</td></table>

In a cell I want to have a formula that gives me the number of homes with dogs. in this case the answer is 3.
In another cell I want to get the number of houses with cats. in this case 2.

Something like: CountUniqueValuesWith("Dog", houseRANGE, petRANGE)

I have gone around chasing my tail on this one.
Formula i need is: "Look in column 2 and find all rows with "Dog". Now take these rows and look in column 1 and return the count of unique house numbers."
I do not want to use a pivot table for this

thanks all,
bf

2. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

Bruce, there may be a formula that will do that, but I am sort of a VBA fan myself, so the attached does what you want, I think...

3. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

Hi Bruce,
you need an extra column to do a little bit of calculation before you can do what you want. Put this in column C, and copy it down ...

=1/SUM(IF(B2=\$B\$2:\$B\$7,IF(A2=\$A\$2:\$A\$7,1,0),0))
entering with CTRL-SHIFT-ENTER instead of ENTER, and putting in whatever ranges you need to cover all houses and pets.

After doing this, another formula, like this ...
=SUM(IF(\$B\$2:\$B\$7="dog",\$C\$2:\$C\$7,0))
again entered with CTRL-SHIFT-ENTER instead of ENTER, entered into any empty cell will do the trick.

Cheers, Glenn.

4. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

Mike I like the VBA approach, but your example gives me the opposite of what I am looking for.
Your function tells me how many dogs a house has.
I want to know how many houses have dogs. In the example I gave, the answer is 3 houses.
Also I may have not made it clear that this is a flat data log file and that the first and second record are referring to the same dog so even in your example of counting dogs your function returns 2 dogs for house 1, when in fact the answer should be 1 dog.

I need to know the count of *unique* houses that have dogs. The function would need to find all the rows with "dog" (rows 1,2,4,and 5) then determine the count of *unique* house numbers (house 1{which is listed twice}, 2, and 3) for a total of 3 houses.

Glenn's formula array works exactly as needed however I would much rather have a function so i don't have to add the extra column to make it work.

Bruce

5. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

Glenn, you are a "formula array genius",
You have gotten me out of a pinch! Thanks a bunch!
I don't understand exactly why it works, but it does. I'll get a grasp of formula arrays some day.

I am hoping that Mike or someone else may have a function solution so I don't have to add another column to the data.

Thanks again!
Bruce

6. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

Here are two functions. The first one can be used as an array function to actually list the unique items corresponding to the type of pet. The second one uses the first one to count the number of unique items corresponding to the type of pet.

<pre>Option Explicit

Option Base 1

Function UniqueItems(critString As String, RangeCrit As Range, RangeIn As Range) _
As Variant
Dim Unique() As Variant ' array that holds the unique items
Dim Element As Range
Dim NumUnique As Integer
Dim i As Integer, j As Integer
Dim FoundMatch As Boolean
Dim SearchArray() As Variant
' Counter for number of unique elements
NumUnique = 0
j = 0
For i = 1 To RangeCrit.Cells.Count
If RangeCrit.Cells(i).Value = critString Then
j = j + 1
ReDim Preserve SearchArray(j)
SearchArray(j) = i
End If
Next i

' Loop through the searcharray
For i = 1 To UBound(SearchArray)
FoundMatch = False

' Has item been added yet?
For j = 1 To NumUnique
If RangeIn.Cells(SearchArray(i)).Value = Unique(j) Then
FoundMatch = True
End If
Next j

' If not in list, add the item to unique list
NumUnique = NumUnique + 1
ReDim Preserve Unique(NumUnique)
Unique(NumUnique) = RangeIn.Cells(SearchArray(i)).Value
End If

Next i
UniqueItems = Unique
End Function
</pre>

<pre>Function CountUniqueItems(critString As String, RangeCrit As Range, RangeIn As Range) _
As Integer
CountUniqueItems = UBound(UniqueItems(critString, RangeCrit, RangeIn))
End Function
</pre>

Assume your example is in A1:B7, then

critString is e.g. equal to "dog"
Rangecrit is B2:B7
RangeIn is A2:A7

7. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

If you don't like arrays and are not a programmer, you could create a third column that concatenates the first two then subtotals on the third column to get a quick answer.

HTH

8. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

Sorry 'bout that Bruce, I think I was influenced by external sources <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> when I looked at your post last night.

9. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

Bruce,

the following array-formula will give you the desired counts:

=SUM(IF(ISNUMBER(SEARCH(D2,UNIQUEVALUES(\$A\$2:\$A\$7& \$B\$2:\$B\$7))),1))

where A2:A7 holds the house numbers, B2:B7 the pet types, and D2 a condition/criterion like "dog".

In case it's needed: To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

http://longre.free.fr/english/index.html

10. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

... as I was thinking about this, another quick way to get what you are after is using a Pivot Table against the original data. Select all data, set the left column as House, Top as Animal, and sum on count of animal....
HTH

11. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

Pivot Table. Definitely. Especially if some household acquires a pet alligator or hedgehog. Pivots automatically expand.

12. ## Re: Count unique records based on criteria of 2nd colu (Excel(2000))

hp,
Thanks a bunch! this is exactly what I was looking for.
Just had to make a minor change to error trap for the possibility of an empty array (no dogs found) and it worked like a charm!

Thanks again to everyone,
Bruce

#### Posting Permissions

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