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

Topic

New Reply

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.

House	Pet Type
1	dog
1	dog
1	cat
2	dog
3	dog
3	cat

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

Reply | Quote

Replies

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…

Reply | Quote

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.

Thanks for your response,
Bruce

Reply | Quote

Sorry ’bout that Bruce, I think I was influenced by external sources when I looked at your post last night.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

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
                GoTo AddItem '(Exit For-Next loop)
            End If
        Next j
       
AddItem:
'       If not in list, add the item to unique list
        If Not FoundMatch Then
            NumUnique = NumUnique + 1
            ReDim Preserve Unique(NumUnique)
            Unique(NumUnique) = RangeIn.Cells(SearchArray(i)).Value
        End If
    
    Next i
 UniqueItems = Unique
End Function

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

Assume your example is in A1:B7, then

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Bruce,

If you’re willing to download Longre’s Morefunc add-in and add it to your system,
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.

Morefunc is downloadable from:

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

Aladin

Reply | Quote

… 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

Reply | Quote

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

Reply | Quote