SUMIF, then formula assistance requested

[pnguyen]

Hi, I would like to ask for assistance for a formula that would calculate the following for me.

In cell, D3, I would like a formula that would say, "If column A contains "SMITH" and "Gone Home," add the logged in time for that status. I would like to be able to then drag the formula and it'll catch the next person, "Hallman" and "Butcher." Thank you for your help.

<table border="0" cellpadding="0" cellspacing="0" width="742"><col style="width: 125pt;" width="166"> <col style="width: 76pt;" width="101"> <col style="width: 57pt;" width="76"> <col style="width: 44pt;" width="58"> <col style="width: 48pt;" width="64"> <col style="width: 44pt;" width="58"> <col style="width: 164pt;" width="219"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; width: 125pt;" height="17" width="166">SMITH</td> <td class="xl78" style="border-left: medium none; width: 76pt;" width="101">Flags</td> <td class="xl78" style="border-left: medium none; width: 57pt;" width="76">Gone Home</td> <td class="xl78" style="border-left: medium none; width: 44pt;" width="58">Available</td> <td class="xl78" style="border-left: medium none; width: 48pt;" width="64">Follow Up</td> <td class="xl78" style="border-left: medium none; width: 44pt;" width="58">Available</td> <td class="xl78" style="border-left: medium none; width: 164pt;" width="219">Available, No ACD, Away from desk</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Logged In</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Acd Logged
In</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:00</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:11:44</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Available</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:06:07</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Follow Up</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:30</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Available</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:01:04</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Available, No ACD</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:06</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Available</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:07:14</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Away from desk</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:04:03</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Available</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:34:29</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Follow Up</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:30</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Available</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:37:24</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Follow Up</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:30</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; border-top: medium none;" height="17">HALLMAN</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Flags</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Logged In</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Acd Logged
In</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:00</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:06</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Supervisor Projects</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">06:32:37</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Available</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:31:44</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Supervisor Projects</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">01:54:27</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:05</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:00</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">08:58:59</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; border-top: medium none;" height="17">BUTCHER</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Flags</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Logged In</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">Acd Logged
In</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:00</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:12</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:00</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:00:00</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Gone Home</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:03:24</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt; border-top: medium none;" height="17">Available</td> <td class="xl78" style="border-top: medium none; border-left: medium none;">:58:20</td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl78" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>

 

[Steve=True]

Something like this might work for you:

=INDEX(A:A,MATCH("Gone Home :00:00 ",A1:A35,0)-3,1)