I have a spreadsheet with 12 worksheets, one for each month. I would like to
be able to use a 13th worksheet to add amounts from all 12 pertaining to a
date range specified on each sheet. I've tried sumif but the formula would
be VERY long and complex.

Thanks!

Ok, so fill in the details.

What is the format of your sheet names?
Jan, Feb, Mar etc?
January, February, March?
1-09, 2-09, 3-09?
Jan 09, Feb 09, Mar 09 ?

Where is this data?

SUMIF based on what condition?

--
Biff
Microsoft Excel MVP


"EricSHEM" wrote in message
...
I have a spreadsheet with 12 worksheets, one for each month. I would like
to
be able to use a 13th worksheet to add amounts from all 12 pertaining to a
date range specified on each sheet. I've tried sumif but the formula
would
be VERY long and complex.

Thanks!

Thanks for the reply.

My worksheets are labeled using this format:
Oct08, Nov08, Dec08, Jan09.....

On the 13th sheet I want to collect dollar amounts from column W on the
other 12. The date the payment was made is in column V. Since this is for
tracking credit card purchases/payments the dates for a the monthly billing
cycle must be used (cycle starts on the 28th and ends on the 27th of the
following month). Sometimes items are purchased in one month but the payment
is made the following billing cycle. For example I could purchase something
in October but due to a back order or shipping snafu it may not arrive until
November and be on the December billing cycle. Therefore the October
purchase would actually count against the December monthly limit.

Let me know if you need anymore specifics.




"T. Valko" wrote:

Ok, so fill in the details.

What is the format of your sheet names?
Jan, Feb, Mar etc?
January, February, March?
1-09, 2-09, 3-09?
Jan 09, Feb 09, Mar 09 ?

Where is this data?

SUMIF based on what condition?

--
Biff
Microsoft Excel MVP


"EricSHEM" wrote in message
...
I have a spreadsheet with 12 worksheets, one for each month. I would like
to
be able to use a 13th worksheet to add amounts from all 12 pertaining to a
date range specified on each sheet. I've tried sumif but the formula
would
be VERY long and complex.

Thanks!

Ok, this formula is a thing of beauty! <g

If you have 12 sheets and the first sheet is for Oct08 then the 12th sheet
must be for Sep09.

I'm assuming the sheet name format is mmmyy (3 letter month, 2 digit year -
Jul09)

Use cells to hold the date boundaries:

A1 = start date
B1 = end date

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(DATE(2008,10+{ 0,1,2,3,4,5,6,7,8,9,10,11},1),"mmmyy")&"'!V1:V10") ,"="&A1,INDIRECT("'"&TEXT(DATE(2008,10+{0,1,2,3,4 ,5,6,7,8,9,10,11},1),"mmmyy")&"'!W1:W10")))-SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(DATE(2008,10+{0 ,1,2,3,4,5,6,7,8,9,10,11},1),"mmmyy")&"'!V1:V10"), ""&B1,INDIRECT("'"&TEXT(DATE(2008,10+{0,1,2,3,4,5 ,6,7,8,9,10,11},1),"mmmyy")&"'!W1:W10")))


--
Biff
Microsoft Excel MVP


"EricSHEM" wrote in message
...
Thanks for the reply.

My worksheets are labeled using this format:
Oct08, Nov08, Dec08, Jan09.....

On the 13th sheet I want to collect dollar amounts from column W on the
other 12. The date the payment was made is in column V. Since this is
for
tracking credit card purchases/payments the dates for a the monthly
billing
cycle must be used (cycle starts on the 28th and ends on the 27th of the
following month). Sometimes items are purchased in one month but the
payment
is made the following billing cycle. For example I could purchase
something
in October but due to a back order or shipping snafu it may not arrive
until
November and be on the December billing cycle. Therefore the October
purchase would actually count against the December monthly limit.

Let me know if you need anymore specifics.




"T. Valko" wrote:

Ok, so fill in the details.

What is the format of your sheet names?
Jan, Feb, Mar etc?
January, February, March?
1-09, 2-09, 3-09?
Jan 09, Feb 09, Mar 09 ?

Where is this data?

SUMIF based on what condition?

--
Biff
Microsoft Excel MVP


"EricSHEM" wrote in message
...
I have a spreadsheet with 12 worksheets, one for each month. I would
like
to
be able to use a 13th worksheet to add amounts from all 12 pertaining
to a
date range specified on each sheet. I've tried sumif but the formula
would
be VERY long and complex.

Thanks!