Count With Date Problem

[rbdude]

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.

 

[Biff]

Hi!

Try this:

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

Biff

 

[RagDyer]

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!
==============================================