Urgent: IF(AND) function not calculating correctly?

[ssvv]

I am creating a worksheet which will later be used to run monthly pivot tables. However, payroll is done on a weekly bases (Sunday -Saturday). Since, one week may overlap into two months (ex. Dec. 26, 2010 - January 1, 2011) I was trying to use a IF(And) formula to proportionately distribute a week's payroll into appropriate months.

The total values for facility in Oregon (on the 'Overlapping Week Info.' worksheet):

<TABLE style="WIDTH: 812pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1077><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3746" width=107><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3048" span=2 width=87><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3397" width=97><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3700" width=106><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4026" width=115><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4352" width=125><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2536" width=73><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 4980" width=143><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 4770" width=137><TBODY><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 12.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 height=17 width=107>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=87>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=87>C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=97>D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=106>E</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=115>F</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=125>G</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=73>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=143>I </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 103pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl73 width=137>J</TD></TR><TR style="HEIGHT: 26pt" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=35>Facility</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Code</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 width=87>Company</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Reg Hrs</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>OT Hrs</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Reg $</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>OT $</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Pay Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>Pay Period Start</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>Pay Period End</TD></TR><TR style="HEIGHT: 12.5pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=17>Oregon</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72>UIL</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>291.71</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>0.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>4667.44</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66>0.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>01/06/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>12/26/10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>01/01/11</TD></TR></TBODY></TABLE>


I use the following formula to divide the total values into month of December and January:

Formula in Cell C2:
=IF(AND(G2='Overlapping Week Info.'!$H:$H,B2='Overlapping Week Info.'!$B:$B),('Overlapping Week Info.'!$D:$D*((I2-H2+1)/7)),0)

<TABLE style="WIDTH: 544pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=724><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4538" width=130><COL style="WIDTH: 48pt" span=6 width=64><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2536" span=2 width=73><TBODY><TR style="HEIGHT: 14.5pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=130>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>E</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>F</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>G</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=73>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=73>I </TD></TR><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 39pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=52 align=right>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=130>Facility</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>Facility Code</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>Regular Hours</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>Overtime Hours</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>Regular Earnings</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>Overtime Earnings</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>Pay Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=73>Pay Period Start Date</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=73>Pay Period End Date</TD></TR><TR style="HEIGHT: 14.5pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 align=right>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=130>Oregon</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>UIL</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>250</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>4000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64 align=right>1/6/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=73 align=right>12/26/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=73 align=right>12/31/2010</TD></TR><TR style="HEIGHT: 14.5pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 align=right>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=130>Oregon</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>UIL</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64 align=right>1/6/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=73 align=right>1/1/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 55pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=73 align=right>1/1/2011</TD></TR></TBODY></TABLE>

I use a similar formula in C2 and C3. In C2 I get the correct answer however, I don't get the right answer in C3.


What am I doing wrong? I pressed F9 to refresh also.

Please help!!

Thank you!!!

 

[AlphaFrog]

This...
=IF(AND(G2='Overlapping Week Info.'!$H:$H,B2='Overlapping Week Info.'!$B:$B),('Overlapping Week Info.'!$D:$D
...is not a valid method for a two criteria lookup.

Below uses the SUMPRODUCT function to do the two criteria lookup. There are a couple of other methods as well.

If you have Excel 2003 or earlier, you have to use a fixed range size with SUMPRODUCT. Cannot reference an entire column e.g. $H:$H. If you have Excel 2007 or later, I think you can reference an entire column with SUMPRODUCT, but then you probably would just use the SUMIFS function instead. Technically this isn't a lookup. It sums column D for each row that matches both criteria. If only one row matches, that's the lookup. If you have excel 2007 or later, SUMIFS would achieve the same result.

If in the future you could state what version of Excel you're using, that would be helpful.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Facility</td><td style="font-weight: bold;text-align: center;;">Facility Code</td><td style="font-weight: bold;text-align: center;;">Regular Hours</td><td style="font-weight: bold;text-align: center;;">Overtime Hours</td><td style="font-weight: bold;text-align: center;;">Regular Earnings</td><td style="font-weight: bold;text-align: center;;">Overtime Earnings</td><td style="font-weight: bold;text-align: center;;">Pay Date</td><td style="font-weight: bold;text-align: center;;">Pay Period Start Date</td><td style="font-weight: bold;text-align: center;;">Pay Period End Date</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Oregon</td><td style="text-align: center;;">UIL</td><td style="text-align: center;;">250</td><td style="text-align: center;;">0</td><td style="text-align: center;;">4000</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1/6/2011</td><td style="text-align: center;;">12/26/2010</td><td style="text-align: center;;">12/31/2010</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Oregon</td><td style="text-align: center;;">UIL</td><td style="text-align: center;;">42</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1/6/2011</td><td style="text-align: center;;">1/1/2011</td><td style="text-align: center;;">1/1/2011</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">G2='Overlapping Week Info.'!$H$2:$H$100</font>),--(<font color="Red">B2='Overlapping Week Info.'!$B$2:$B$100</font>),--(<font color="Red">'Overlapping Week Info.'!$D$2:$D$100</font>)</font>)*(<font color="Blue">(<font color="Red">I2-H2+1</font>)/7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">G3='Overlapping Week Info.'!$H$2:$H$100</font>),--(<font color="Red">B3='Overlapping Week Info.'!$B$2:$B$100</font>),--(<font color="Red">'Overlapping Week Info.'!$D$2:$D$100</font>)</font>)*(<font color="Blue">(<font color="Red">I3-H3+1</font>)/7</font>)</td></tr></tbody></table></td></tr></table><br />

 

[ssvv]

Thanks a lot!!!

Do I need to have "--" in the formula? If so, please explain why?


Thanks.

 

[AlphaFrog]

Thanks a lot!!!

Do I need to have "--" in the formula? If so, please explain why?

You're welcome.

The "--" is called double unary operators. It's an efficient method to coerce the True\False results of the comparisons within the SUMPRODUCT formula into 1s and 0s.

This explains the SUMPRODUCT function.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html