count text with * wildcard and date range

[Hanna L]

Hi,
I would like to count the number of times a character string occurs in a URL
in a certain date range. Say I'm looking for all the URLs that came from
livejournal.com from the day of 2/15.

A B
Sarah.livejournal.com/123 2/15
georgeB.blogspot.com/George 2/15
happy.typepad.com/blah 2/15
male.livejournal.com/male 2/15
blogger/12345678 2/20
bobtheman.livejournal.com/#bob 2/20

I have tried the SUMPRODUCT function, but it doesn't seem to be able to deal
with the either the wildcard or the date in column B.

SUMPRODUCT ((A1:A5 = "*livejournal*" ),(B1:B5 = "2/15" ))

This usually gives me a result of 0, even though it should be 2.

I have tried fiddling with the comma or multiplier between the two arrays.

Does anyone have any other ideas?

Thank you!

 

[T. Valko]

Use cells to hold the criteria.

D2 = livejournal.com
E2 = 2/15/2010

This will work in any modern version of Excel:

=SUMPRODUCT(--(ISNUMBER(SEARCH(D2,A2:A7))),--(B2:B7=E2))

This will work in Excel 2007 and later:

=COUNTIFS(A2:A7,"*"&D2&"*",B2:B7,E2)