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.


Excel 2010
ABCDEFGHI
1SiteTYPE11/30/201412/31/20141/31/20152/28/20153/31/20154/30/20155/31/2015
2Region 1Full-Time5,1245,5985,0024,5315,1245,0986,483
3Region 1Temp15,48012,8109,5198,3938,7375,3232,826
4Region 2Full-Time2,9604,1343,8163,6073,7913,7633,754
5Region 2Temp1,8052,8231,8471,7291,5601,7511,337
6Region 3Full-Time1,5501,5821,4181,3141,3451,7651,637
7Region 3Temp4,4575,8296,2966,2489,2816,8315,891
8
9PARAMETERSRegion 3Temp3/31/2015
10
11RESULTS
1244,835
1344,835
Sheet1
Cell Formulas
RangeFormula
C12=SUMPRODUCT((Timetbl[Site]=$C$9)*(Timetbl[TYPE]=$D$9)*(Timetbl[[#Headers],[11/30/2014]:[5/31/2015]]>=$E$9)*(Timetbl[[11/30/2014]:[5/31/2015]]))
C13=SUMPRODUCT((Timetbl[Site]=$C$9)*(Timetbl[TYPE]=$D$9)*(TEXT(Timetbl[[#Headers],[11/30/2014]:[5/31/2015]],"MM/DD/YYYY")>=$E$9)*(Timetbl[[11/30/2014]:[5/31/2015]]))


Thanks for your help.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
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.:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,095
Messages
5,835,356
Members
430,351
Latest member
ddalton

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