Results 1 to 8 of 8

20080624, 11:50 #1
 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.

20080624, 12:24 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 Posts
Re: Need Formula for Count (2003/2007)
Here's one way, using an intermediate column.
Regards,
Rory
Microsoft MVP  Excel

20080624, 12:25 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20080624, 12:36 #4
 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.

20080624, 13:38 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20080624, 13:45 #6
 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.

20080624, 17:16 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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.
Regards,
Rory
Microsoft MVP  Excel

20080625, 17:34 #8
 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