=SumProduct((A1:A5="White")*(B1:B5<DateValue("01/06/03")))

--
Regards,
Tom Ogilvy


"Simon" wrote in message
...
I think some programming is needed for what I require,
because I counld not find any functions to achieve it.

White 10/02/03
Blue 11/02/03
Red 12/02/03
Red 12/07/03
White 12/07/03

I need a function to tell me for each colour who many
have dates before 01/06/03.

So producing a function that I pass the String of the
colour and a set date (01/06/03) and it returns the
number of occurances of that colour before the specified
date.

Hopefully I have made it clear what required of the
function.

I hope some will have some idea.

Many Thanks

HI!

I was looking earlier for something very similiar.

What you are probable going to use is a Array Formula.

This is a sample of what I think will do it:

=SUM(IF($D$12:$D$15="blue",IF($E$12:$E$15<DATEVALU E("1/5/2003"),1,0),0))

If you have the add-in, Conditional Sum would suffice.