summing difference cell values across worksheets

[Kgoodman]

I am trying to create a formula that will allow me to sum all the values in a
chart for a certain date. So across 5 sheets, I have a total sheet, and I
want the "January 31st" value in the "completion" column to automatically
update based on the values in "January 31st" under the "completion" column
for the other worksheets. I know this is possible if the cells were the same
- but they are not. Is there a way to create a formula based on searching for
the date name?

Thank you!

 

[T. Valko]

Is the "completion" column in the same location on each sheet? What column
is it? What column contians the dates? What are the sheet names? Are they
unique or do they follow some sort of pattern like: Week1, Week2, Week3 etc.
?

 

[Kgoodman]

yes, the completion column is in the same location on all sheets. Column C.
The dates are in Column A. Sheet names are "Total, S1, S2, S3"

 

[T. Valko]

Ok, try this:

A2 = 1/31/2009

Based on your sheet names being: S1, S2 and S3.

Dates in the range A1:A10
Values to sum in the range C1:C10.

=SUMPRODUCT(SUMIF(INDIRECT("S"&{1,2,3}&"!A1:A10"),A2,INDIRECT("S"&{1,2,3}&"!C1:C10")))

 

[Ashish Mathur]

Hi,

I would suggest that you consolidate all the data in one worksheet first and
then use the SUM() function. Since the cell is not the same on all the
worksheets, please make use of Data > Consolidate to bring all info on one
worksheet.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com