Sum by date rage using multiple sheets

There is a worksheet for each month of the year and I am trying to sum on the
13th sheet based on a random "from date" and "to date". I am having trouble
creating ranges in the formula that span the 12 monthly worksheets. I
couldn't get named ranges to work.

what I have so far:

=SUMPRODUCT(--(?range?$E$1:$AI$1>=DATE(2006,3,3)),--(?range?$E$1:$AI$1<=DATE(2006,3,20)),--(?range?))

where "?range?" is what I am tying to figure out...
Ultimately I will change the dates to reference cells on sheet 13 for the
"from date" and "to date" values.

In sheets 1-12 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.) and are consistant in each sheet (besides the total days in
the month)

There are around 100 rows on each sheet

Example: Begin date is Feb 25, end date is March 5, I need all rows summed
for the range of columns that are within the from and to date range for
sheet 2 and sheet 3, an example of a sheet for 3 is below:

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
around a 100 more categories ongoing below

I need the formula result to show on sheet 13.

category 1 total here
Category 2 total here
etc. etc.

Thanks!

The current layout of you sheets makes this a real mess. You really need to
put all your data in a single sheet with 3 columns: Category, Date,Value.
With that layout you can EASILY do all sorts of calculations, summaries,
reports, Pivot Tables, etc.

Spending an hour reworking your layout will save you dozens of hours of pain
and effort required by what you now have.


"Michael" wrote:

> There is a worksheet for each month of the year and I am trying to sum on the
> 13th sheet based on a random "from date" and "to date". I am having trouble
> creating ranges in the formula that span the 12 monthly worksheets. I
> couldn't get named ranges to work.
>
> what I have so far:
>
> =SUMPRODUCT(--(?range?$E$1:$AI$1>=DATE(2006,3,3)),--(?range?$E$1:$AI$1<=DATE(2006,3,20)),--(?range?))
>
> where "?range?" is what I am tying to figure out...
> Ultimately I will change the dates to reference cells on sheet 13 for the
> "from date" and "to date" values.
>
> In sheets 1-12 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.) and are consistant in each sheet (besides the total days in
> the month)
>
> There are around 100 rows on each sheet
>
> Example: Begin date is Feb 25, end date is March 5, I need all rows summed
> for the range of columns that are within the from and to date range for
> sheet 2 and sheet 3, an example of a sheet for 3 is below:
>
> 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
> around a 100 more categories ongoing below
>
> I need the formula result to show on sheet 13.
>
> category 1 total here
> Category 2 total here
> etc. etc.
>
> Thanks!

I am stuck in that a client of mine has been accumulating data this way for
remote locations, summing manually, and entering as a journal entry. I had
hoped to pull it into another sheet to summarize and import the entry into
the accounting software.

Based on your reply and my restraint to keep the format, I suppose I can
pull the data to a single sheet from the 1st 12 sheets and summarize from
that.

Thank you for your input.

"Duke Carey" wrote:

> The current layout of you sheets makes this a real mess. You really need to
> put all your data in a single sheet with 3 columns: Category, Date,Value.
> With that layout you can EASILY do all sorts of calculations, summaries,
> reports, Pivot Tables, etc.
>
> Spending an hour reworking your layout will save you dozens of hours of pain
> and effort required by what you now have.
>
>
> "Michael" wrote:
>
> > There is a worksheet for each month of the year and I am trying to sum on the
> > 13th sheet based on a random "from date" and "to date". I am having trouble
> > creating ranges in the formula that span the 12 monthly worksheets. I
> > couldn't get named ranges to work.
> >
> > what I have so far:
> >
> > =SUMPRODUCT(--(?range?$E$1:$AI$1>=DATE(2006,3,3)),--(?range?$E$1:$AI$1<=DATE(2006,3,20)),--(?range?))
> >
> > where "?range?" is what I am tying to figure out...
> > Ultimately I will change the dates to reference cells on sheet 13 for the
> > "from date" and "to date" values.
> >
> > In sheets 1-12 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.) and are consistant in each sheet (besides the total days in
> > the month)
> >
> > There are around 100 rows on each sheet
> >
> > Example: Begin date is Feb 25, end date is March 5, I need all rows summed
> > for the range of columns that are within the from and to date range for
> > sheet 2 and sheet 3, an example of a sheet for 3 is below:
> >
> > 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
> > around a 100 more categories ongoing below
> >
> > I need the formula result to show on sheet 13.
> >
> > category 1 total here
> > Category 2 total here
> > etc. etc.
> >
> > Thanks!

Another option may be to use a UNION statement in Access to create a view that consolidates the data from the multiple worksheets and allows the Pivot Table to connect as if it is looking at data in a single sheet.

a post at
http://www.excelforum.com/showthread...ighlight=union
describes how to do this for convering data in multiple columns in a spreadsheet into a single column but I see no reason why it would not work connecting multiple worksheets. I have not tested it... but it can't be worse than trying to use 'multiple consolidation ranges'

The process would be to create an Access DB, then create a linked table for each page with data in your source spreadsheet, then write the query that does the union of all the linked tables. In another spreadsheet you can then create a pivot table using 'external data' and attach to the query. You will then see all data as if it is in one table and can do whatever you need to do in the pivot table.

If you are going to have to do this more than once I expect it will be worth the effort.

regards...