sumifs 2 criteria in different sheets

[junmacs]

Hi,

I need help in sumifs, i have an excel file that need a formula to calculate no. of drawings per discipline by week.
Excel file attahced.

[davsth]

what are your expected answers?

[junmacs]

hi,

i'm trying find the sum of the total no. drawings by discipline per week.

[davsth]

but do you have fractional answers? Your first row suggests it would contribute 0.494 drawings per week that are inhouse arch, Is that correct? is that what you are looking for?

[junmacs]

yes, and i can't figure it out how to get.

[davsth]

=SUMPRODUCT((Sheet2!$E$14:$E$31<=Sheet3!$E7)*(Sheet2!$E$14:$E$31>=Sheet3!$D7)*(Sheet2!$F$14:$F$31-Sheet2!$E$14:$E$31+1)*(Sheet2!I$14:I$31)*(1/Sheet2!$H$14:$H$31))

would be along the lines of what you want, but will not work. Merged cells are always the enemy and rows 18:20 mess the whole formula up.

[junmacs]

hi,

it didn't work.

[junmacs]

Hi,
It works already, thank you so much.
I just adjust the merge cell.

[davsth]

I am not sure it returns the correct values as
*(Sheet2!$F$14:$F$31-Sheet2!$E$14:$E$31+1)

really wants to be min(data end date, week end date)-max(data start date-week start date)+1 and I have not worked out how to put this in the formula yet!

[junmacs]

Thank you so much.
I will try to use this min(data end date, week end date)-max(data start date-week start date)+1.