SUMPRODUCT with date range

[Kristine Pacia]

Good Day!

If someone can help me, how do i sumproduct Total Hrs Worked in "Monthly Summary" Sheet in "Weekly Summary" Sheet C11 given the date range from "Weekly Summary" Sheet B5 to B6?

Thank you.

 

[Hui]

Do you want to:
Distribute the Weekly Summary data into the Monthly Summary worksheet ?
or
Summarize the Monthly Summary data in the Weekly Summary worksheet according to the date ranges?

 

[Kristine Pacia]

Summarize the Monthly Summary data in the Weekly Summary worksheet according to the date ranges

 

[bosco_yip]

1] Some data put in "Weekly Summary" sheet A11:H13 for testing purpose

2] In "Monthly Summary" sheet D7, formula copied across and down :

=IF($A7="","",IF((D$5>='Weekly Summary '!$B$5)*(D$5<='Weekly Summary '!$B$6),IFERROR(1/(1/VLOOKUP($A7,'Weekly Summary '!$A$11:$H$300,MATCH(INDEX($4:$4,MATCH("zz",$A$4:D$4)),'Weekly Summary '!$A$10:$H$10,0),0)),""),""))

Regards
Bosco

 

[Kristine Pacia]

Hi "Monthly Sheets" are the input cells and shall not contain formula. The "Weekly Summary" sheet shall be the one consolidating the entries in "Monthly Sheets" based on the date range in "Weekly Summary Sheet" B5 and B6

 

[Kristine Pacia]

Anyone who can help?

 

[Hui]

On the weekly summary worksheet

B11: =INDEX('Monthly Summary'!$B$7:$B$20,MATCH($A11,'Monthly Summary'!$A$7:$A$20,0))
C11: =SUMPRODUCT(('Monthly Summary'!$D$7:$AH$20)*('Monthly Summary'!$D$5:$AH$5>=$B$5)*('Monthly Summary'!$D$5:$AH$5<=$B$6)*('Monthly Summary'!$A$7:$A$20=$A11))
D11: =SUMPRODUCT(('Monthly Summary'!$AJ$7:$BN$20)*('Monthly Summary'!$AJ$5:$BN$5>=$B$5)*('Monthly Summary'!$AJ$5:$BN$5<=$B$6)*('Monthly Summary'!$A$7:$A$20=$A11))
E11: =SUMPRODUCT(('Monthly Summary'!$BP$7:$CT$20)*('Monthly Summary'!$BP$5:$CT$5>=$B$5)*('Monthly Summary'!$BP$5:$CT$5<=$B$6)*('Monthly Summary'!$A$7:$A$20=$A11))
F11: =SUMPRODUCT(('Monthly Summary'!$CV$7:$DZ$20)*('Monthly Summary'!$CV$5:$DZ$5>=$B$5)*('Monthly Summary'!$CV$5:$DZ$5<=$B$6)*('Monthly Summary'!$A$7:$A$20=$A11))
G11: =SUMPRODUCT(('Monthly Summary'!$EB$7:$FF$20)*('Monthly Summary'!$EB$5:$FF$5>=$B$5)*('Monthly Summary'!$EB$5:$FF$5<=$B$6)*('Monthly Summary'!$A$7:$A$20=$A11))
H11: =SUMPRODUCT(('Monthly Summary'!$FH$7:$GL$20)*('Monthly Summary'!$FH$5:$GL$5>=$B$5)*('Monthly Summary'!$FH$5:$GL$5<=$B$6)*('Monthly Summary'!$A$7:$A$20=$A11))


Change Row 20 in the above formula to refer to the bottom of your data range
Then Copy B11:H11 Down

What are the formula in the Monthly Summary worksheet doing ?