Summing over multiple worksheets
 

Hi. I have weekly data by Contract number over multiple worksheets. I want
to sum the hours associated with the contract. The contract information will
not always be in the same cell, as new contracts are constantly added. My
question is, should I use an If and then a SumIf or would a SumIf by
sufficient?

=SUMIF("'Apr 4:Jun 27'!, A5:A33",A5,"'Apr 4:Jun 27'!, G5:G33")

I'm obviously missing something here. I want this formula to look in
worksheets Apr 4 to Jun 27 in cells A5 through A33 and if there is a match to
cell A5 on my summary worksheet, then add corresponding figures in cells G5
through G33 and then put into cell G5 on my summary worksheet.

Help please :)

Summing over multiple worksheets
 

Try this...

I'm assuming the sheet names are the *Sunday* dates for a period.

A1 = 4/4/2010 (Apr 4) = first sheet to include
B1 = 6/27/2010 (Jun 27) = last sheet to include

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(A1-7+ROW(INDIRECT("1:"&(B1-A1)/7+1))*7,"mmm
d")&"'!A5:A33"),A5,INDIRECT("'"&TEXT(A1-7+ROW(INDIRECT("1:"&(B1-A1)/7+1))*7,"mmm
d")&"'!G5:G33")))

--
Biff
Microsoft Excel MVP


"Alberta Rose" wrote in message
...
Hi. I have weekly data by Contract number over multiple worksheets. I
want
to sum the hours associated with the contract. The contract information
will
not always be in the same cell, as new contracts are constantly added. My
question is, should I use an If and then a SumIf or would a SumIf by
sufficient?

=SUMIF("'Apr 4:Jun 27'!, A5:A33",A5,"'Apr 4:Jun 27'!, G5:G33")

I'm obviously missing something here. I want this formula to look in
worksheets Apr 4 to Jun 27 in cells A5 through A33 and if there is a match
to
cell A5 on my summary worksheet, then add corresponding figures in cells
G5
through G33 and then put into cell G5 on my summary worksheet.

Help please :)