Sumifs error in formula

[Grimzby]

Hello...i am trying to collect data that is true to 2 criteria from sheets in a workbook the formula i am trying is =SUMIFS("Week"&ROW(1:1)&"!F436:L436","Week"&ROW(1:1)&"!F6:L6","=9001","Week"&ROW(1:1)&"!F7:L7","=230108000") any help is much appreciated.

[arthurbr]

Hi,
you might need the SUMPRODUCT function.
Lots of examples on this forum
Cheers

[Grimzby]

Hi...I also tried =SUMPRODUCT((INDIRECT("Week"&ROW(1:1)&"!F6:L6")=9001)*(INDIRECT("Week"&ROW(1:1)&"!F7:L7")=230108000),INDIRECT("Week"&ROW(1:1)&"!F434:L434"))....but no luck..
Thanks

[arthurbr]

Could you post a sample of your data and describe what went wrong?

[Grimzby]

Hello....here is my project i am working on.....i an trying to set the criteria' contract ticket number cells F6:L6 and contract number cells F7:L7 then look for the results in F434:L434 I would like to have the formula set up so i can use the auto fill to change the weeks as i drag down in the HOURS sheet.
Thanks
The file is to large for uploading to here so i have a link to it.
[url]http://rapidshare.com/files/94957946/TIMESHEET_EXAMPLE.zip

In this workbook there would be sheets week1 thru week 52.

[arthurbr]

Don't seem to be able to download yur file. You can attach a small sample of it with your message

[Grimzby]

Hello....here is my project i am working on.....i an trying to set the criteria' contract ticket number cells F6:L6 and contract number cells F7:L7 then look for the results in F434:L434 I would like to have the formula set up so i can use the auto fill to change the weeks as i drag down in the HOURS sheet.
Thanks
In this workbook there would be sheets week1 thru week 52.

[arthurbr]

Hi Grimzby,
sorry couldn't look earlier.
I don't understand what the contract number is good for.
I'm afraid the use of merged cells will also create problems in the future.

[Grimzby]

Hi arthur....and thanx for replying...there could be 2 different contract# and still have the same contract ticket#..ie:9001 0230108000 or 9001 0230136000.
Thanks

[arthurbr]

Sorry Grimzby,
at my new workplace I can't open the file, but as I recall your summary file did not take contract numers into account. is this correct?

[Grimzby]

Hi Arthur...yes thats right...the sample file is my original with only one criteria which was contract ticket # and i would like to add the contract # as the second criteria.

[arthurbr]

Try

Please Login or Register  to view this content.

The last range is the OT or DT range