sum across 12 worksheets

[EricSHEM]

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!

 

[T. Valko]

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?

 

[EricSHEM]

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]

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")))