Sumproduct count between dates for specific day

[Hud67]

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.

 

[Pete_UK]

Assuming column A is Area, B is Start and C is End, and that you have
100 rows in Sheet1, and further assume that you have dates in column A
of Sheet2, then try this in B1 of Sheet2:

=SUMPRODUCT(Sheet1!A$1:A$100="Orange")*(Sheet1!B$1:B$100<=A1)*(Sheet1!C
$1:C$100>=A1))

Adjust the ranges to suit, and then copy down.

Hope this helps.

Pete

 

[Don Guillett]

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

 

[Hud67]

Dear Pete
Thanks for that, but it's not working. Here an example
Sheet1 (thats the database):
Area Start End
Orange 04/01/2008 12/01/2008
Orange 12/01/2008 15/01/2008
Blue 07/01/2008 10/01/2008
Yellow 05/01/2008 09/01/2008

Sheet2 (calculations) with the following example formula for Cell B
=SUMPRODUCT(Sheet1!$A$2:$A$100="Yellow")*(Sheet1!$B$2:$B$100<=$A2)*(Sheet1!$C$2:$C$100>=$A2)

As you see all the results are 0
Date Yellow Blue Orange
01/01/2008 0 0 0
02/01/2008 0 0 0
03/01/2008 0 0 0
04/01/2008 0 0 0
05/01/2008 0 0 0
06/01/2008 0 0 0
07/01/2008 0 0 0
08/01/2008 0 0 0
09/01/2008 0 0 0
10/01/2008 0 0 0
11/01/2008 0 0 0
12/01/2008 0 0 0
13/01/2008 0 0 0
14/01/2008 0 0 0
15/01/2008 0 0 0

And here is a manually result list (as it should be with formulas):
Date Yellow Blue Orange
01/01/2008
02/01/2008
03/01/2008
04/01/2008 1
05/01/2008 1 1
06/01/2008 1 1
07/01/2008 1 1 1
08/01/2008 1 1 1
09/01/2008 1 1 1
10/01/2008 1 1
11/01/2008 1
12/01/2008 2
13/01/2008 1
14/01/2008 1
15/01/2008 1
16/01/2008

Thanks again for help.
Daniel

 

[Hud67]

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

 

[daddylonglegs]

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))

 

[Don Guillett]

Try this. I used one page. Modify to suit.

Sub CountInDate()
For i = 2 To Cells(Rows.Count, "d").End(xlUp).Row
For ii = 2 To Cells(Rows.Count, "b").End(xlUp).Row
If Cells(i, "d") >= Cells(ii, "b") _
And Cells(i, "d") <= Cells(ii, "c") Then
Select Case UCase(Cells(ii, "a"))
Case Is = "ORANGE": WC = "e"
Case Is = "BLUE": WC = "f"
Case Is = "YELLOW": WC = "g"
Case Else
End Select
Cells(i, WC) = Cells(i, WC) + 1
End If
Next ii
Next i
End Sub

 

[Hud67]

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