Formula Help

rehberger

Board Regular
Joined
Aug 28, 2013
Messages
52
Hello, I was hoping to get some help on a formula on the below data spreadsheet snapshot. I was looking for a way to pull totals by job title for the date entered. I have another sheet set up where the user can change the date and would like the info to update. Ex. if the user changes the date to 1/2/14 the Supervisor line would read 62 (adding all supervisors for that day 9+5+37+11)

DeptJob Title1/1/20141/2/20141/3/20141/4/2014
100SPECIALIST14089204138
100SUPERVISOR59916
100SUPERVISOR0566
100SUPERVISOR39374040
100SUPERVISOR1111611
100TECHNICIAN94426449
100TECHNICIAN38707042
100TECHNICIAN18888

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
maybe somthing like...

Where H1 contains the Job title and I1 contains the date


H2=SUMIF($B$2:$B$9,H1,INDEX($C$2:$F$9,,MATCH(I1,$C$1:$F$1,0)))

A
B
C
D
E
F
G
H
I
1
Dept</SPAN>
Job Title</SPAN>
1/1/2014 </SPAN>
1/2/2014 </SPAN>
1/3/2014 </SPAN>
1/4/2014 </SPAN>
supervisor</SPAN>
1/2/2014 </SPAN>
2
100</SPAN>
SPECIALIST</SPAN>
140</SPAN>
89</SPAN>
204</SPAN>
138</SPAN>
62</SPAN>
3
100</SPAN>
SUPERVISOR</SPAN>
5</SPAN>
9</SPAN>
9</SPAN>
16</SPAN>
4
100</SPAN>
SUPERVISOR</SPAN>
0</SPAN>
5</SPAN>
6</SPAN>
6</SPAN>
5
100</SPAN>
SUPERVISOR</SPAN>
39</SPAN>
37</SPAN>
40</SPAN>
40</SPAN>
6
100</SPAN>
SUPERVISOR</SPAN>
11</SPAN>
11</SPAN>
6</SPAN>
11</SPAN>
7
100</SPAN>
TECHNICIAN</SPAN>
94</SPAN>
42</SPAN>
64</SPAN>
49</SPAN>
8
100</SPAN>
TECHNICIAN</SPAN>
38</SPAN>
70</SPAN>
70</SPAN>
42</SPAN>
9
100</SPAN>
TECHNICIAN</SPAN>
18</SPAN>
8</SPAN>
8</SPAN>
8</SPAN>

<TBODY>
</TBODY>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top