Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF and INDIRECT | Excel Worksheet Functions | |||
sumif + indirect | Excel Discussion (Misc queries) | |||
SUMIF + INDIRECT? | Excel Worksheet Functions | |||
SUMIF INDIRECT | Excel Worksheet Functions | |||
Combine Indirect and Sumif | Excel Worksheet Functions |