counting by date

[ch1]

I have columns like below

01/02/2002 50
01/02/2002 25
01/05/2002 30
20/05/2002 60
01/06/2002 70 etc

I need to do a count of the qty's by date.

total before todays date
total within the next week
then every week after that

Any ideas??

I have tried datevalue, but it needs the actual date. I want it to look into the cell and recognise the date

something like cell A1:A10 < 17/05/2002


Thanks

[Aladin Akyurek]

On 2002-05-18 13:10, ch1 wrote:
I have columns like below

01/02/2002 50
01/02/2002 25
01/05/2002 30
20/05/2002 60
01/06/2002 70 etc

I need to do a count of the qty's by date.

total before todays date
total within the next week
then every week after that

Any ideas??

I have tried datevalue, but it needs the actual date. I want it to look into the cell and recognise the date

something like cell A1:A10 < 17/05/2002


Thanks

=SUMIF(A2:A10,"<"&TODAY(),B2:B10)

for a total before today.

=SUMIF((A2:A10,">=&TODAY(),B2:B10)-SUMIF(A2:A10,">"&TODAY()+7,B2:B10)

for a total within the next week.

Etc.




[ This Message was edited by: Aladin Akyurek on 2002-05-18 13:24 ]

[ch1]

Thanks the first 1 works, but the second gives me everything in the future.

How do I split the future down to the ones in the next 7 days
and then a count of 7-14 days
and 14-21 days etc

[Nimrod]

Do you want to create an sheet that would show each week or do you just want to be able to enter a date and have the weeks total show in one cell ?

[ch1]

Thanks the first 1 works, but the second gives me everything in the future.

How do I split the future down to the ones in the next 7 days
and then a count of 7-14 days
and 14-21 days etc

[ch1]

What I would like to show is

Overdue 200
next week 50
7 - 14 days 20
14 -21 days 30


Thanks

[Nimrod]

Have you thought of creating a Subtotal by week using the Toolbar..Data..subtotals command ?

1- if dates are in colmnA and Totals in colB
put the forumal =WEEKNUM(A1) in columnc.
2- goto Toolbar..Data..subtotals and Sum at each change of Column C

[Aladin Akyurek]

On 2002-05-18 13:31, ch1 wrote:
What I would like to show is

Overdue 200
next week 50
7 - 14 days 20
14 -21 days 30


Thanks

I edited my original post. Did you see it?

[ch1]

Thanks to both, I will go and play


Many thanks

[Aladin Akyurek]

On 2002-05-18 13:37, ch1 wrote:
Thanks to both, I will go and play
Many thanks

Here an example regarding the SUMIF approach:

Microsoft Excel - aaSumif ch1.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
H12		=
*	A	B	C	D	E	F
1	*	*	*	*	*	*
2	2-Apr-02	50	*	:alert('=TODAY()')>18-May-02	:alert('=D2+7')>25-May-02	:alert('=E2+7')>1-Jun-02
3	5-Apr-02	25	*	:alert('=SUMIF(A2:A12,"%3C"&D2,B2:B12)')>105	:alert('=SUMIF($A$2:$A$12,"%3E="&$D$2,$B$2:$B$12)-SUMIF($A$2:$A$12,"%3E"&E2,$B$2:$B$12)')>130	:alert('=SUMIF($A$2:$A$12,"%3E="&$D$2,$B$2:$B$12)-SUMIF($A$2:$A$12,"%3E"&F2,$B$2:$B$12)')>190
4	7-May-02	30	*	*	*	*
5	20-May-02	60	*	*	*	*
6	25-May-02	70	*	*	*	*
7	30-May-02	60	*	*	*	*
8	4-Jun-02	40	*	*	*	*
9	12-Jun-02	30	*	*	*	*
10	24-Jun-02	20	*	*	*	*
11	30-Jun-02	35	*	*	*	*
12	7-Jul-02	45	*	*	*	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Aladin