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.
Thanks for your help.
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 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Site | TYPE | 11/30/2014 | 12/31/2014 | 1/31/2015 | 2/28/2015 | 3/31/2015 | 4/30/2015 | 5/31/2015 | ||
2 | Region 1 | Full-Time | 5,124 | 5,598 | 5,002 | 4,531 | 5,124 | 5,098 | 6,483 | ||
3 | Region 1 | Temp | 15,480 | 12,810 | 9,519 | 8,393 | 8,737 | 5,323 | 2,826 | ||
4 | Region 2 | Full-Time | 2,960 | 4,134 | 3,816 | 3,607 | 3,791 | 3,763 | 3,754 | ||
5 | Region 2 | Temp | 1,805 | 2,823 | 1,847 | 1,729 | 1,560 | 1,751 | 1,337 | ||
6 | Region 3 | Full-Time | 1,550 | 1,582 | 1,418 | 1,314 | 1,345 | 1,765 | 1,637 | ||
7 | Region 3 | Temp | 4,457 | 5,829 | 6,296 | 6,248 | 9,281 | 6,831 | 5,891 | ||
8 | |||||||||||
9 | PARAMETERS | Region 3 | Temp | 3/31/2015 | |||||||
10 | |||||||||||
11 | RESULTS | ||||||||||
12 | 44,835 | ||||||||||
13 | 44,835 | ||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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.