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!

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)

--
Biff
Microsoft Excel MVP


"Hanna L" <Hanna (E-Mail Removed)> wrote in message
news:8958CE09-D498-41BD-A838-(E-Mail Removed)...
> 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!
>