Sum using Date Ranges..

  • Thread starter Thread starter JMax
  • Start date Start date
J

JMax

Good Morning,
On Sheet1 there are two columns one has the date and the other contain
amounts. There is an amount for every business day for the last
years (Example below):

Column A Column B
2/1/2003 322.5
2/2/2003 445.2
2/3/2003 299.6



I would like to be able to sume the amounts by accounting periods o
Sheet2 which would be the beginning of another analysis but I want t
condense space by showing just the start date and the end date of eac
month with the sum of the amount on column C (Example Belw)

Column A Column B Column C
2/1/2003 2/28/2003 10,000

Any ideas would be appreciated.
Thank you
 
Hi
if your data criteria are in cell A1 and B1 (on sheet 2) enter the
following formula in C1
=SUMPRODUCT(('sheet1'!$A$1:$A$1200>=A1)*('sheet1'!$A$1:$A$1200<=B1),'sh
eet1'!$B$1:$b$1200)
copy down
 
Frank,
Thanks for the prompt response. However, I am not following the logic
Not too mention, I was not specific enough. Sheet1 Column A contain
days of the year. Column F contains the amount of invoices prcocesse
for that day.

A Column F
2/1/2003 17,000
2/2/2003 14,500
2/3/2003 16,333
........
1/31/2003 15,598

On Sheet2 I have 3 columns, Period Start, Period Stop, Monthly Invoic
Amount. I would like the formula to determine monthly invoice amoun
by referencing the Period Start and Period End date on sheet2 an
suming the Daily amounts on Sheet1.

Period Start Period End Amount
2/1/2003 2/29/2003 300,000

I apologize for the redundancy, I just want to make sure I am clear a
well as understand the formula.

Frank said:
*Hi
if your data criteria are in cell A1 and B1 (on sheet 2) enter the
following formula in C1
=SUMPRODUCT(('sheet1'!$A$1:$A$1200>=A1)*('sheet1'!$A$1:$A$1200<=B1),'sh
eet1'!$B$1:$b$1200)
copy down
Click to expand...
 
Hi
just change the formula to
=SUMPRODUCT(('sheet1'!$A$1:$A$1200>=A1)*('sheet1'!$A$1:$A$1200<=B1),'sh
eet1'!$F$1:$F$1200)

this will look for dates in A1:A1220 (sheet1) which are between A1 and
B1 on your second sheet and sums all corresponding values in column F.
To explain this formula:
The first multiplications multiplies boolean values (TRUE/FALSE). Only
if both conditions are met this multiplication return '1'. this is
multiplied with the corresponding value in column F. Then all
multiplications are summed.


--
Regards
Frank Kabel
Frankfurt, Germany
Frank,
Thanks for the prompt response. However, I am not following the
logic. Not too mention, I was not specific enough. Sheet1 Column A
contains days of the year. Column F contains the amount of invoices
prcocessed for that day.

A Column F
2/1/2003 17,000
2/2/2003 14,500
2/3/2003 16,333
.......
1/31/2003 15,598

On Sheet2 I have 3 columns, Period Start, Period Stop, Monthly Invoice
Amount. I would like the formula to determine monthly invoice amount
by referencing the Period Start and Period End date on sheet2 and
suming the Daily amounts on Sheet1.

Period Start Period End Amount
2/1/2003 2/29/2003 300,000

I apologize for the redundancy, I just want to make sure I am clear as
well as understand the formula.
Click to expand...
 
Below is the formula I have entered that is not returning the correc
data.

US Input Sheet Column A contains the days of the year.
US Input Sheet Column F contains the amount of the invoice.
Seasonal Adjustment Sheet B4 contains the Start date.
Seasonal Adjustment Sheet C4contains the End date.

I would like to be able to sum the invoice amounts from US Input Shee
in the Seasonal Adjustment Sheet by just adding the start and end dat
of the month.


=SUMPRODUCT(('US Input'!A2:A1044>='Seasonal Adjustment'!B4)*('U
Input'!A2:A1044<='Seasonal Adjustment'!C4),'US Input'!F2:F1044
 
Hi
the formula looks o.k.. what error did you get?. One idea: the dates in
column A are not real dates but text. If you like, email me your
spreadsheet (frank[dot]kabel[at]freenet[dot]de) and I#ll have a look at
it
 
Hi
unfortunately this attachment seems to be not related to your question.
At least I can't find your date ranges and your SUMPRODUCT formula on
this worksheet?
 
Hi Max
this is a nice mistake :-) -> you will like it:
1. you may change cell B4 from 01/31/2002 to 01/31/2003
2. I also corrected your SUMPRODUCT formula (you're not allowed to use
ranges like A:A. You have to use A1:A1000). In E5 use:
=SUMPRODUCT(('US Input'!$A$1:$A$1000>=SA!$B4)*('US
Input'!A1:A1000<=SA!$C4),'US Input'!$F$1:$F$1000)
 
Feel free to ignore me forever, the formula was not summing properl
because I was using 2002 instead of 2003 in my first cell. I am s
sorry, your sumproduct formula worked.

Thank you!
 
Back
Top