Sum based on date range

[Michael]

Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows
represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.)
How can I sum the rows based on a begin and end date?
Example: Begin date is March 10, end date is March 15, I need row 3 summed
for the range of columns that are within the from and to date range.

3/1 3/2 3/3 3/4 etc.
category 1 50.00 5.00 2.00 3.50
category 2 1.00 10.00 .75 .25

I need the formula result to show on another sheet in one column for each
row, I suppose I could just copy the formula down the column.

Thanks!

 

[Toppers]

Assuming Sheet2 has Category1, Category2 etc in column A, then in column B put:

=SUMPRODUCT(--(Sheet1!$B$1:$W$1>=DATE(2006,3,3)),--(Sheet1!$B$1:$W$1<=DATE(2006,3,20)),--(Sheet1!B2:W2))

for period 3rd to 20th march 2006 inclusive


<b1:w1> contain dates
<b2:w2> contain amounts (for category 1)

Copy down as required

HTH

 

[Biff]

Hi!

One way:

=SUMPRODUCT((D37="category
3")*(E2:AI2>=--"3/10/2006")*(E2:AI2<=--"3/15/2006"),E3:AI7)

Biff

 

[Michael]

Thanks, I was able to get that to work for one sheet then hit another snag...
There is a worksheet for each month of the year and I am trying to sum on a
13th sheet. I am having trouble creating ranges in the formula that span the
12 monthly worksheets. BTW, the assumption below is correct but it is on
sheet 13. Should I or could I used named ranges to accomplish the
multi-sheet dilemma?

 

[Toppers]

Michael,
How do we handle the date ranges for each sheet? It appears
we will need a fomula for each month as the dates will (obviously) be
diferent.

 

[Michael]

There are 12 sheets, one for each month. Row one has the days of the month,
column A contains the categories and to the right of the categories and under
each day of the month values are entered throughout the month for each day.

Sheet one row one contains the dates 1/1/06 through 1/31/06
Sheet two row one contains the dates 2/1/06 through 2/28/06
etc. through sheet twelve. The sheets are named jan, feb, mar, etc.
Sheet 13 would summarize the category values based on a from and to date
range which may cross over more than one sheet but not three.

 

[Toppers]

As an example, for dates 13th Feb to 20th March inclusive this would provide
a (relatively) simple solution:

=SUMPRODUCT(--(Feb!$B$1:$W$1>=DATE(2006,2,13)),--(Feb1!$B$1:$W$1<=DATE(2006,2,28)),--(Feb!B2:W2)) +
=SUMPRODUCT(--(Mar!$B$1:$W$1>=DATE(2006,3,1)),--(Mar!$B$1:$W$1<=DATE(2006,3,20)),--(Mar!B2:W2))

There may be a more "sophisticated" formula but I wouldn't know how to
construct it.

Is this exceptable?

 

[Michael]

I will set it up that way and test it, thanks!

As an example, for dates 13th Feb to 20th March inclusive this would provide
a (relatively) simple solution:

=SUMPRODUCT(--(Feb!$B$1:$W$1>=DATE(2006,2,13)),--(Feb1!$B$1:$W$1<=DATE(2006,2,28)),--(Feb!B2:W2)) +
=SUMPRODUCT(--(Mar!$B$1:$W$1>=DATE(2006,3,1)),--(Mar!$B$1:$W$1<=DATE(2006,3,20)),--(Mar!B2:W2))

There may be a more "sophisticated" formula but I wouldn't know how to
construct it.

Is this exceptable?

 

[Peo Sjoblom]

With your sheet named Jan - Dec the dates in question lower date in A1 and
upper date in B1 on the summary sheet

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}&"'!1:1"),">="&A1,INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}&"'!3:3")))-SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}&"'!1:1"),">"&B1,INDIRECT("'"&{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}&"'!3:3")))

if the sheet names are in the range J1:J12

=SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!1:1"),">="&A1,INDIRECT("'"&J1:J12&"'!3:3")))-SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!1:1"),">"&B1,INDIRECT("'"&J1:J12&"'!3:3")))

both formulas sum row 3, change the 3:3 to another row for another category,
you could let a formula do that as well, assume you have a list of
categories in the summary sheet called MyList and you put the category in C1

=SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!1:1"),">="&A1,INDIRECT("'"&J1:J12&"'!"&MATCH(C1,MyList,0)&":"&MATCH(C1,MyList,0))))-SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!1:1"),">"&B1,INDIRECT("'"&J1:J12&"'!"&MATCH(C1,MyList,0)&":"&MATCH(C1,MyList,0))))



-

Regards,

Peo Sjoblom