Help with Array Function

[Gos-C]

Hi,

I want a formula in column E to show the number of times each name i
column D occur, alone OR second (that is, after the slash (/), i
column A, when column B has the word Red, AND the date in column C i
greater than December 31, 2003 but less than September 1, 2004. An
help?

Here are five items from each column:

Column A
Tom Harry
Don Cross / Sue Yonge
Joe Gibbs / Don Cross
Sue Yonge
Tom Harry / Joe Gibbs

Column B
Red
Red
White
Red
Blue

Column C
25-Mar-2004
02-Aug-2004
16-Dec-2003
12-Sep-2004
31-Jan-2004

Column D
Don Cross
Joe Gibbs
Sue Yonge
Tom Harry

In Column E, for Don Cross, I have:
=SUM(IF((B1:B5="Red")*(C1:C5>37986)*(C1:C5<38231)*((A1:A5="Do
Cross")+(MID(A1:A5,FIND("/",A1:A5)+2,LEN(A1:A5))="Don Cross")),1,0))

It give me #VALUE.

Any help

Thanks

 

[Aladin Akyurek]

Try:

=SUMPRODUCT(--(ISNUMBER(SEARCH(D1,$A$1:$A$5))),--($B$1:$B$5="Red"),--($C$1:$C$5>"31-Dec-03"+0),--($C$1:$C$5<"1-Sep-04"+0))

Needs just enter.