Sumproduct count between dates for specific day

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Hud67 - 17 May 2008 14:39 GMT

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.

Reply to this Message

Pete_UK - 17 May 2008 14:58 GMT

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

> Hello
> I have the following excel database
[quoted text clipped - 15 lines]
> 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.

Reply to this Message

Hud67 - 17 May 2008 17:59 GMT

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

> 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
[quoted text clipped - 28 lines]
> > 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.

Reply to this Message

Don Guillett - 17 May 2008 15:56 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hello
> I have the following excel database
[quoted text clipped - 17 lines]
> calculate between the start and end date. Thank you very much for your
> help.

Reply to this Message

Hud67 - 17 May 2008 18:15 GMT

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

> This formula counts all but it depends on how you want to treat 18 May. I
> used US dates.
[quoted text clipped - 37 lines]
> > calculate between the start and end date. Thank you very much for your
> > help.

Reply to this Message

daddylonglegs - 17 May 2008 19:28 GMT

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

> Dear Don
> Thank you. Sorry but I don't get it. Could you please check my other reply
[quoted text clipped - 43 lines]
> > > calculate between the start and end date. Thank you very much for your
> > > help.

Reply to this Message

Hud67 - 17 May 2008 20:44 GMT

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

> Pete's suggestion should work for you, but you are missing a couple of
> parentheses. Try this amended version:
[quoted text clipped - 48 lines]
> > > > calculate between the start and end date. Thank you very much for your
> > > > help.

Reply to this Message

Don Guillett - 17 May 2008 19:43 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Dear Don
> Thank you. Sorry but I don't get it. Could you please check my other reply
[quoted text clipped - 44 lines]
>> > calculate between the start and end date. Thank you very much for your
>> > help.

Reply to this Message