Period date spacific sums

[schmidtcm]

How do I create a formula that will allow me to sum columns by a spacific date range off of seperate worksheets


EX:


A B C D E

7/5 .5 3.33 .417 2

7/25 3 0 .5 .667

8/6 0 0 0 .333

9/24 0 0 .417 0

10/1 0 .917 .5 .167


Columns B, C, D, and E contains the data i want to add by period dates/column. The period dates are spacific. Period 1 is July 1, 2010 to July 28th 2010. Period 2 is July 29th to Aug 25th.


Column A may have multipule entries for any date. There is no way to know how many lines there will be for each period.

 

[Hui]

Scmidtcm

Have a read about Sumproduct at

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/


The basic sumproduct to do what you want for Column B is

=+SUMPRODUCT(1*(A1:A10>=Date(2010,7,1))*(A1:A10<=date(2010,7,28))*(B1:B10))

Or you can use Sumifs

=+SUMIFS(B1:B10,A1:A10,">="&DATE(2010,7,1),A1:A10,"<="&DATE(2010,7,28))


Change references to Column B to other columns as appropriate.


Now if you want to change it so that you can have a variable amount of data without having to use edit the formula's I would use named ranges


Set a few Named Ranges as follows

ColA: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!A:A),1)

ColB: =OFFSET(ColA,0,COLUMN(Sheet1!B1)-COLUMN(Sheet1!$A$1))

ColC: =OFFSET(ColA,0,COLUMN(Sheet1!C1)-COLUMN(Sheet1!$A$1))

ColD: =OFFSET(ColA,0,COLUMN(Sheet1!D1)-COLUMN(Sheet1!$A$1))

ColE: =OFFSET(ColA,0,COLUMN(Sheet1!E1)-COLUMN(Sheet1!$A$1))


and the change the Sumproduct formulas as below

=+SUMPRODUCT(1*(ColA>=DATE(2010,7,1))*(ColA<=DATE(2010,7,28))*(ColB))


I would also add a DateFrom and DateTo named range so that your formula is now

=+SUMPRODUCT(1*(ColA>=DateFrom)*(ColA<=DateTo)*(ColB))


Repeat for Columns C..E