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!