Sumproduct with Date not giving expected results

MTsims

New Member
Joined
Dec 8, 2011
Messages
2
I have a small table that I'm using SUMPRODUCT to find the region, type and the last three months worth of total monthly hours. The results come back showing the sum of the entire for the region and type and totally overlooks the date parameter I have in the formula. My date filed in the formula is >= a certain date. What do I need to add to the formula in order for it to give me only the last three months of data? An example is below.

I tried it a few different ways, but with the same results. The parameters are for Region 3, Temp, and which months the total hours are for. I expect to get 22,004 but instead it's always 44,835.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;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;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">Site</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">TYPE</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">11/30/2014</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">12/31/2014</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">1/31/2015</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">2/28/2015</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">3/31/2015</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">4/30/2015</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;color: #FFFFFF;background-color: #4F81BD;;">5/31/2015</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DCE6F1;;"> Region 1 </td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">Full-Time</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">5,124 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">5,598 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">5,002 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">4,531 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">5,124 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">5,098 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">6,483 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"> Region 1 </td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Temp</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;;">15,480 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;;">12,810 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;;">9,519 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;;">8,393 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;;">8,737 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;;">5,323 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;;">2,826 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DCE6F1;;"> Region 2 </td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">Full-Time</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">2,960 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">4,134 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">3,816 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">3,607 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">3,791 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">3,763 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;background-color: #DCE6F1;;">3,754 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"> Region 2 </td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Temp</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;;">1,805 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;;">2,823 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;;">1,847 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;;">1,729 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;;">1,560 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;;">1,751 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #00B050;;">1,337 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #DCE6F1;;"> Region 3 </td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DCE6F1;;">Full-Time</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;background-color: #DCE6F1;;">1,550 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;background-color: #DCE6F1;;">1,582 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;background-color: #DCE6F1;;">1,418 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;background-color: #DCE6F1;;">1,314 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;background-color: #DCE6F1;;">1,345 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;background-color: #DCE6F1;;">1,765 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #16365C;background-color: #DCE6F1;;">1,637 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;;"> Region 3 </td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Temp</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #16365C;;">4,457 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #16365C;;">5,829 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #16365C;;">6,296 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #16365C;;">6,248 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #16365C;;">9,281 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #16365C;;">6,831 </td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #16365C;;">5,891 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="font-weight: bold;border-right: 1px solid black;;">PARAMETERS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Region 3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Temp</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/31/2015</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">RESULTS</td><td style="font-weight: bold;text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 44,835 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"> 44,835 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" 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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">C12</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Timetbl[Site]=$C$9</font>)*(<font color="Red">Timetbl[TYPE]=$D$9</font>)*(<font color="Red">Timetbl[[#Headers],[11/30/2014]:[5/31/2015]]>=$E$9</font>)*(<font color="Red">Timetbl[[11/30/2014]:[5/31/2015]]</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C13</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Timetbl[Site]=$C$9</font>)*(<font color="Red">Timetbl[TYPE]=$D$9</font>)*(<font color="Red">TEXT(<font color="Green">Timetbl[[#Headers],[11/30/2014]:[5/31/2015]],"MM/DD/YYYY"</font>)>=$E$9</font>)*(<font color="Red">Timetbl[[11/30/2014]:[5/31/2015]]</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Thanks for your help.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,909
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board!

Table headers are evaluated as text, not as numeric date values, converting them to numeric values in the formula should fix the problem

=SUMPRODUCT((timetbl[Site]=$C$9)*(timetbl[TYPE]=$D$9)*(DATEVALUE(timetbl[[#Headers],[30/11/2014]:[31/05/2015]])>=$E$9)*(timetbl[[30/11/2014]:[31/05/2015]]))
 

MTsims

New Member
Joined
Dec 8, 2011
Messages
2
Excellent!! That worked - I usually can find answers to my Excel question by searching the site, but not this one.

Thanks for your help.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top