Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Formula for Count (2003/2007)

    I need a count formula. Please see attached sheet.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need Formula for Count (2003/2007)

    Here's one way, using an intermediate column.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Formula for Count (2003/2007)

    You can use a custom VBA function for this:

    Function CountUniqueDates(rngDates As Range, strName As String) As Long
    Dim col As New Collection
    Dim oCell As Range
    On Error Resume Next
    For Each oCell In rngDates
    If oCell.Offset(0, 1) = strName Then
    col.Add Item:=oCell, Key:=CStr(oCell)
    End If
    Next oCell
    CountUniqueDates = col.Count
    End Function

    Enter the following formula in H8:

    =CountUniqueDates($C$8:$C$32,G8)

    and fill down. See attached version.
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Formula for Count (2003/2007)

    I am amazed. I kicked around sumif and and it was a "no go" for me.
    Rory could I trouble you for little insight on how the sumproduct formula works that you inserted?
    I would appreciate it.
    Hans thankyou for your help also.

    I accomplished this with a "Pivot Table" but I just don't understand how they work enough for me to comfortably use them.
    I would much rather stick to formulas and code. You guys are the best.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Formula for Count (2003/2007)

    Let's look at Rory's intermediate formulas first.

    In cell E8, the expression SUMPRODUCT(($D$8:$D$32=D8)*($C$8:$C$32=C8)) counts the number of rows that match D8 and C8. This is 5, since there are 5 rows with the combination 03/09/2007 and Bill. So 1/SUMPRODUCT(($D$8:$D$32=D8)*($C$8:$C$32=C8)) evaluates to 1/5 = 0.2.
    The cells in column E for these 5 rows add up to 5*0.2 = 1.

    Similarly, in E13, the expression SUMPRODUCT(($D$8:$D$32=D13)*($C$8:$C$32=C13)) counts the number of rows that match D13 and C13. This is 3, since there are 3 rows with the combination Mark and 12/08/2007. So 1/ SUMPRODUCT(($D$8:$D$32=D13)*($C$8:$C$32=C13)) evaluates to 1/3 ~ 0.33333.
    The cells in column E for these 3 rows add up to 3*0.33333 = 1.

    The SUMIF formulas in column H add all values in column E for a particular person. For Mark, this results in the 3*0.33333 = 1 mentioned above, since there is one unique date for Mark. But for Bill, it results in 5*0.2+5*0.2 = 1+1 = 2 since there are two unique dates for Bill, each of whom occurs 5 times. If the second date had occurred only 4 times, the result would still have been 2, for 5*0.2+4*0.25 = 1 + 1 = 2 too.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Formula for Count (2003/2007)

    WOW! I'll have to review that a few times. But I really appreciate your time and generosity.

    Thankyou again.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need Formula for Count (2003/2007)

    Hans has already explained the formula version using an intermediate column (thanks, Hans! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>) but you can also do it without the intermediate column - see attached.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Formula for Count (2003/2007)

    THANKS AGAIN

Posting Permissions

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