Hi
It works perfect!
You are all superstars.
Thanks a lot.

By the way, i have another small question. I have also amounts in another
row and would like to sum them up for the month (related to the start date)
an (e.g. yellow etc.).
What would be the correct sumproduct formula?

Thanks again

"daddylonglegs" wrote:

Pete's suggestion should work for you, but you are missing a couple of
parentheses. Try this amended version:

=SUMPRODUCT((Sheet1!$A$2:$A$100="Yellow")*(Sheet1! $B$2:$B$100<=$A2)*(Sheet1!$C$2:$C$100=$A2))

"Hud67" wrote:

Dear Don
Thank you. Sorry but I don't get it. Could you please check my other reply
with an example.
Cheers
Daniel

"Don Guillett" wrote:

This formula counts all but it depends on how you want to treat 18 May. I
used US dates.
=SUMPRODUCT(($A$2:$A$22=$D$1)*($B$2:$B$22=$D$2+ROW (A1))+($C$2:$D$22=$D$2+ROW(A1)))
area start end orange
orange 05/15/2008 05/18/2008 05/14/2008 05/14/2008
orange 05/18/2008 05/27/2008 2 05/15/2008
blue 04/01/2008 04/25/2008 0 05/16/2008
blue 05/02/2008 05/17/2008 2 05/17/2008
yellow 04/28/2008 05/17/2008 3 05/18/2008
0 05/19/2008
0 05/20/2008
0 05/21/2008
0 05/22/2008
0 05/23/2008
0 05/24/2008
0 05/25/2008
0 05/26/2008
1 05/27/2008
0 05/28/2008

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hud67" wrote in message
...
Hello
I have the following excel database

Area Start End
Orange 15/05/08 18/05/08
Orange 18/05/08 27/05/08
Blue 01/04/08 25/04/08
Blue 02/05/08 17/05/08
Yellow 28/04/08 17/05/08
etc.

On a separat worksheet i would like to calculate the following for every
day
of the year.

01/01/08 How many Orange where active on that day
01/02/08 etc.

I tried it with summproduct but can't figure out the correct formula to
calculate between the start and end date. Thank you very much for your
help.