Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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...
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Thanks for your response,
    Bruce

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    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
    </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. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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
    Regards,

    Gary
    (It's been a while!)

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Microsoft MVP - Excel

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    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
    Regards,

    Gary
    (It's been a while!)

  11. #11
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  12. #12
    Star Lounger
    Join Date
    Aug 2001
    Location
    GA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •