Sumif, Index, Match Formula

[rehberger]

Hello, I have formula I cant figure out. With the data below on a separate spreadsheet I am trying to pull the sum of the hours by date by matching just the Code, Dept, Job, Pay. I also wanted the user to be able to change the date and have the new data pull. Ex. the user types in 1/2/2014 the result would be 14 for Supervisor with a Pay of H and 48 for Supervisor with an S. I still need the Code and Dept in the formula since other departments use the same job description. Thanks for any help!

Code	Dept	Job	Pay	Job Code	Position Title	Position Code	1/1/2014	1/2/2014	1/3/2014
100	SLOTS	SUPERVISOR	H	SU007	SLOT SUPERVISOR	078KCSL04	5	9	9
100	SLOTS	SUPERVISOR	H	SU007	SLOT TECH SUPERVISOR	078KCSL05	7	5	6
100	SLOTS	SUPERVISOR	S	SU007	SLOT SUPERVISOR	078KCSL04	39	37	40
100	SLOTS	SUPERVISOR	S	SU007	SLOT TECH SUPERVISOR	078KCSL05	11	11	6

<colgroup><col><col><col><col span="2"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Weazel]

Ok, a little more involved then your original question but maybe...


=SUMPRODUCT((A2:A5=A8)*(B2:B5=B8)*(C2:C5=C8)*(D2:D5=D8)*(H1:J1=E8)*(H2:J5))

	A	B	C	D	E	F	G	H	I	J
1	Code</SPAN>	Dept</SPAN>	Job</SPAN>	Pay</SPAN>	Job Code</SPAN>	Position Title</SPAN>	Position Code</SPAN>	1/1/2014 </SPAN>	1/2/2014 </SPAN>	1/3/2014 </SPAN>
2	100</SPAN>	SLOTS</SPAN>	SUPERVISOR</SPAN>	H</SPAN>	SU007</SPAN>	SLOT SUPERVISOR</SPAN>	078KCSL04</SPAN>	5</SPAN>	9</SPAN>	9</SPAN>
3	100</SPAN>	SLOTS</SPAN>	SUPERVISOR</SPAN>	H</SPAN>	SU007</SPAN>	SLOT TECH SUPERVISOR</SPAN>	078KCSL05</SPAN>	7</SPAN>	5</SPAN>	6</SPAN>
4	100</SPAN>	SLOTS</SPAN>	SUPERVISOR</SPAN>	S</SPAN>	SU007</SPAN>	SLOT SUPERVISOR</SPAN>	078KCSL04</SPAN>	39</SPAN>	37</SPAN>	40</SPAN>
5	100</SPAN>	SLOTS</SPAN>	SUPERVISOR</SPAN>	S</SPAN>	SU007</SPAN>	SLOT TECH SUPERVISOR</SPAN>	078KCSL05</SPAN>	11</SPAN>	11</SPAN>	6</SPAN>
6
7	Code</SPAN>	Dept</SPAN>	Job</SPAN>	Pay</SPAN>	Date</SPAN>
8	100</SPAN>	SLOTS</SPAN>	SUPERVISOR</SPAN>	H</SPAN>	1/2/2014 </SPAN>	14</SPAN>

<TBODY>
</TBODY>

 

[AlphaFrog]

Try something like this where H1:Z1 have the dates to lookup and Date is the lookup date value

=SUMIFS(INDEX(H:Z,0,MATCH(Date,H1:Z1,0)), D:D,"H", A:A, 100, B:B, "SLOTS")

The SumIfs function is available in Excel 2007 or later. The bolded text determines the date column to SUM

 

[rehberger]

Awesome! Thanks for everyone's help.