Counting text and dates

[Steve]

I have one column with names, the other with dates. Each name has multiple
rows of different dates.
I'd like to count the number of times each individual name has a date
occurrence before a specific date.
e,g.,
A B
Smith 7/31/2009
8/3/2009
7/24/2009
7/25/2009
8/4/2009

Jones 7/26/2009
8/5/2009
8/8/2009
7/31/2009

Specific date is 7/31/2009, so Smith would have 2, and Jones would have 1.

Thanks a million.

 

[Jacob Skaria]

First of all fill in the names to all rows.. and use the below formula

C1 = Smith
D1 = 7/31/2009 (in the same format as col B)

In E1 try the below formula
=SUMPRODUCT((A1:A100=C1)*(B1:B100<D1))

To fill the names in all rows try the below

--Select a unused cell say E2
--Enter the formula in cell E2 which references the top cell (=E1)
--Copy the cell
--Keeping the copy select the data range in ColA
--Press F5. From Goto window select blanks
--This will select all blanks.. Now Right click>PasteSpecial>Formulas. will
fill in with the data you need..

If this post helps click Yes