Formula help - indirect & sumif & dates .....
 

Hi, here's my situation ...

Workbook with ~30 tabs, sheetnames are a 3-digit number (some starting
with 0) + 1 tab which is to be a summary of the others. On each of
the 'base-data' sheets, range A2:A366 is a date, col B:Y are data
columns to be summarized. On the summary sheet, each sheet will have
12 rows -- col A as sheetname, col B as Month, col C:Z summed data
columns.

So, in Col C of the Summary sheet, the formula needs to go to the
correct sheet and sum the values in col B according to Month (as
derived from the date).

Any help is greatly appreciated!

Regards,
ray

Formula help - indirect & sumif & dates .....
 

=SUMIF(INDIRECT("'"&$A2&"'!A2:A366"),$B2,OFFSET(IN DIRECT("'"&$A2&"'!B2:Y366"),0,COLUMN(A1)-1))

and copy across

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ray" wrote in message
ps.com...
Hi, here's my situation ...

Workbook with ~30 tabs, sheetnames are a 3-digit number (some starting
with 0) + 1 tab which is to be a summary of the others. On each of
the 'base-data' sheets, range A2:A366 is a date, col B:Y are data
columns to be summarized. On the summary sheet, each sheet will have
12 rows -- col A as sheetname, col B as Month, col C:Z summed data
columns.

So, in Col C of the Summary sheet, the formula needs to go to the
correct sheet and sum the values in col B according to Month (as
derived from the date).

Any help is greatly appreciated!

Regards,
ray

Formula help - indirect & sumif & dates .....
 

Bob,

The Summary sheet uses Months (format:MMM) , while the source sheets
use days (format: MM/DD/YY) -- how would I modify your suggested
formula to make this conversion?

thanks, ray

Formula help - indirect & sumif & dates .....
 

=SUMPRODUCT(--(Format(INDIRECT("'"&$A2&"'!A2:A366"),"mmm")=$B2), OFFSET(INDIRECT("'"&$A2&"'!B2:Y366"),0,COLUMN(A1)-1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ray" wrote in message
ups.com...
Bob,

The Summary sheet uses Months (format:MMM) , while the source sheets
use days (format: MM/DD/YY) -- how would I modify your suggested
formula to make this conversion?

thanks, ray