Sum subject to a bounded timeline

[rodrigje]

Hi all,

I have been trying to get a sum for three columns on a monthly basis. I would like to have a TOTAL amount at the bottom of columns or a given cell) C, D, and E but only if they are say from 01/26/04 (the 26th of the previous month) until 02/25/04 (After the 25th they are added to the next month information). This list actually has an entire year data but for brevity just pasted this info. My efforts to o a sumif statement arent working. Also, would i have to manually make changes in the formula for each month or is there a way for Excel to know that I want sums bound between the 26th of previous month to the 25th of current month the

Thanx a bunch everyone!, Sample file is below:

A B C D E
01/26/04 02/01/04 6,942 2,599 3,013
01/27/04 02/01/04 2,398 800 6,886
01/28/04 02/01/04 2,191 868 5,188
01/29/04 02/01/04 2,277 886 6,728
01/30/04 02/01/04 2,520 828 6,669
01/31/04 02/01/04 0 0 0
02/01/04 02/01/04 0 0 0
02/02/04 02/08/04 4,590 8 3,658
02/03/04 02/08/04 3,641 2,819 6,185
02/04/04 02/08/04 2,325 892 5,351
02/05/04 02/08/04 2,097 926 5,951
02/06/04 02/08/04 2,923 1,092 5,693
02/07/04 02/08/04 0 0 0
02/08/04 02/08/04 0 0 0
02/09/04 02/15/04 6,743 3,690 3,697
02/10/04 02/15/04 2,125 771 6,185
02/11/04 02/15/04 2,109 911 5,676
02/12/04 02/15/04 2,149 890 6,273
02/13/04 02/15/04 2,204 858 6,208
02/14/04 02/15/04 0 0 0
02/15/04 02/15/04 0 0 0
02/16/04 02/22/04 0 0 0
02/17/04 02/22/04 8,189 3,559 3,091
02/18/04 02/22/04 2,367 939 5,515
02/19/04 02/22/04 2,409 902 7,071
02/20/04 02/22/04 2,547 960 4,555
02/21/04 02/22/04 0 0 0
02/22/04 02/22/04 0 0 0
02/23/04 02/29/04 6,869 2,940 5,574
02/24/04 02/29/04 2,283 742 6,527
02/25/04 02/29/04 2,404 893 6,689
02/26/04 02/29/04 2,218 1,134 5,128
02/27/04 02/29/04 2,601 900 3,951
02/28/04 02/29/04 0 0 0
02/29/04 02/29/04 0 0 0

 

[Fergus]

Try adding the following,

Book1
	G	H	I	J
1
2	First date	Last date
3	26/1/04	25/2/04	74,302
Sheet1

Formula in I3 is: =SUMPRODUCT(--(A1:A100>=G3),--(A1:A100<=H3),C1:C100)

HTH

 

[Fergus]

Alternatively put this formula in F2 and drag down : =IF(DAY(A2)=25,SUM($C$2:C2)-SUM($F1:F$2),0)

 

[rodrigje]

I apologize, allow me to clarify. I would like to get monthly totals like below.

A B C
Jan Total
Feb Total
Mar Total
Apr Total
May Total
Jun Total
Jul Total
Aug Total
Sep Total
Oct Total
Nov Total
Dec Total
Jan Total


Where the data pulled for say Feb Total includes data from Jan 15 to Feb15. from the data listed below for example,

Date A B C

12/24/04 3,665 1,636 2,597
12/25/04 0 0 0
12/26/04 0 0 0
12/27/04 4,360 1,828 2,094
12/28/04 2,173 786 3,652
12/29/04 1,082 650 4,967
12/30/04 3,732 763 5,395
12/31/04 2,249 1,409 4,339
01/01/05 0 0 0
01/02/05 0 0 0
01/03/05 4473 1902 3349
01/04/05 2198 722 5397
01/05/05 2146 1573 4393
01/06/05 3110 65 3781
01/07/05 1349 1712 4578
01/08/05 3145 778 2477
01/09/05 0 0 0
01/10/05 4106 2387 2993
01/11/05 2170 484 6119
01/12/05 2206 625 5128
01/13/05 2189 822 5024
01/14/05 2153 831 5175
01/15/05 1971 820 3350
01/16/05 0 0 0
01/17/05 3649 1931 2716
01/18/05 2105 745 4817
01/19/05 2116 952 4285
01/20/05 2328 907 5403
01/21/05 2255 1322 4662
01/22/05 2470 880 4719
01/23/05 0 0 0
01/24/05 3835 1923 960
01/25/05 2070 726 4837
01/26/05 1973 951 5167
01/27/05 2487 821 5037
01/28/05 2040 816 5145
01/29/05 2152 884 3473
01/30/05 0 0 0
01/31/05 3681 1697 2924
02/01/05 1911 705 5856
02/02/05 1842 686 4939
02/03/05 2071 775 5397
02/04/05 2004 872 4540
02/05/05 2124 914 3101
02/06/05 0 0 0
02/07/05 3557 3620 2463
02/08/05 2930 684 4032
02/09/05 1858 814 6529
02/10/05 2318 770 4751
02/11/05 1949 910 4482
02/12/05 1807 965 3328
02/13/05 0 0 0
02/14/05 3585 1911 1587
02/15/05 1730 658 4515
02/16/05 1982 868 5425
02/17/05 2187 752 4369
02/18/05 2049 823 4246
02/19/05 931 859 2327
02/20/05 0 0 0
02/21/05 0 0 0
02/22/05 5925 3095 2917
02/23/05 2322 873 5750
02/24/05 2260 818 4591
02/25/05 2081 981 4542
02/26/05 2153 1322 3790
02/27/05 0 0 0
02/28/05 4714 2148 2069
03/01/05 2234 792 4455
03/02/05 2006 848 5221
03/03/05 2276 812 4343
03/04/05 2008 822 4691
03/05/05 1896 808 3985


I sure could use some guidance as I do feel lost (the data set is huge- Years) Any advice is great thanx

 

[Fergus]

Sorry, now I am away up-country Thailand with a very very slow connection. If no-one else answers I'll have a look again on 4 Jan 2006. Until then, have a Happy New Year.

 

[rodrigje]

OK and thanks for replting!!

 

[Fergus]

Hi Rod,

Looks like noone else picked this up. First question : which column of numbers do you wish to sum for each month, and second question : do you want the sum from 25th to 25th (as per your first post) or 15th to 15th (as per your second post)?

Anyway, I have assumed that you want to sum each column individually and you want the sum to be from 14th of one month to 15th of next month, both dates inclusive.

Two ways you could try, both based upon the suggestions I gave you before. One way is to get your monthly totals in a column running down beside your data, so that on each 15th of the month you have your total. Thus assuming you have your dates in column A starting in A2 (allowing row 1 for headings) and numbers in columns B, C & D, you can put the following formula in E2 and fill down : =IF(DAY($A2)=15,SUM(B$2:B2)-SUM(E1:E$2),0

That will give you the monthly total of column B to the 15th of each month. You can fill across into cols F and G to get the monthly totals of columns C & D

On the other hand, if you want your totals in a separate table as you indicate, then try using the table shown below, which I put in cells I2:L15

Book1
	I	J	K	L
2	Dec-04
3	Jan-05	48,477	19,793	74,808
4	Feb-05	62,847	28,839	109,665
5	Mar-05	37,024	16,621	62,721
6	Apr-05	0	0	0
7	May-05	0	0	0
8	Jun-05	0	0	0
9	Jul-05	0	0	0
10	Aug-05	0	0	0
11	Sep-05	0	0	0
12	Oct-05	0	0	0
13	Nov-05	0	0	0
14	Dec-05	0	0	0
15	Jan-06	0	0	0
Sheet1

The date in column I are entered as the 15th of the month in question then formatted as mmm-yy. The formula in J3 is : =SUMPRODUCT(--($A$1:$A$100<=$I3),--($A$1:$A$100>$I2),B$1:B$100) which can be filled down and across.

I hope that the above gives you enough basis to work on for your particular circumstances.

 

[rodrigje]

That is great! It works like a charm, and you just saved me alot of time!

Thank you.

 

[Fergus]

My pleasure - incidentally there was an eror in my last post, that probably didn't matter, in that I said I was assuming you wanted your sums from 14th of the month, whereas it should have read from 16th of the month. The formulae, as you will have discovered, correctly worked from the 16th.