I have the following matrix and I want to count the number of rows where the
string "DOG" occurs anywhere within column A cells and the date in column B
is less than today's date (assume it is 6/7/05). In this example, I would
expect the result to be 2 (for matches in Row 1 and row 4.

Row 1 DOG 1/1/05
Row 2 DOG, CAT 9/1/06
Row 3 CAT 1/1/05
Row 4 DOG 4/1/05
Row 5 DOG 10/1/05

I tried somthing like
{=SUM(ISNUMBER((SEARCH("dog",A1:A5,1)))*TODAY()<B1 :B5}, but it doesn't work.
I did enter Ctl+Shift+Enter after entering the formula.

Does anyone know how I can correct this forumula? Thanks in advance.

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("dog",A1:A15))),--(ISNUMBER(B1:B15)),--(B1:B15<TODAY()))

Biff

"rbdude" wrote in message
...
I have the following matrix and I want to count the number of rows where
the string "DOG" occurs anywhere within column A cells and the date in
column B is less than today's date (assume it is 6/7/05). In this example,
I would expect the result to be 2 (for matches in Row 1 and row 4.

Row 1 DOG 1/1/05
Row 2 DOG, CAT 9/1/06
Row 3 CAT 1/1/05
Row 4 DOG 4/1/05
Row 5 DOG 10/1/05

I tried somthing like
{=SUM(ISNUMBER((SEARCH("dog",A1:A5,1)))*TODAY()<B1 :B5}, but it doesn't
work. I did enter Ctl+Shift+Enter after entering the formula.

Does anyone know how I can correct this forumula? Thanks in advance.

Say that you designate two cells to contain the date and the string, so that
they can be easily changed without having to revise the formula.

D1 = date
D2 = string

=SUMPRODUCT((ISNUMBER(SEARCH(D2,A1:A100))*(B1:B100 <D1)))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"rbdude" wrote in message
...
I have the following matrix and I want to count the number of rows where
the
string "DOG" occurs anywhere within column A cells and the date in column
B
is less than today's date (assume it is 6/7/05). In this example, I would
expect the result to be 2 (for matches in Row 1 and row 4.

Row 1 DOG 1/1/05
Row 2 DOG, CAT 9/1/06
Row 3 CAT 1/1/05
Row 4 DOG 4/1/05
Row 5 DOG 10/1/05

I tried somthing like
{=SUM(ISNUMBER((SEARCH("dog",A1:A5,1)))*TODAY()<B1 :B5}, but it doesn't
work.
I did enter Ctl+Shift+Enter after entering the formula.

Does anyone know how I can correct this forumula? Thanks in advance.