# Thread: Need Formula for Count (2003/2007)

1. ## Need Formula for Count (2003/2007)

I need a count formula. Please see attached sheet.

2. ## Re: Need Formula for Count (2003/2007)

Here's one way, using an intermediate column.

3. ## 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.

4. ## 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. ## 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. ## 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. ## 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.

8. ## 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
•