summing time spent per individual for a specific event

[Sparda142]

Hello Excel Gods!
Please help!ray:ray:

In Cell b2 i need a formula that will find a2(john) in column J and sum the same type of event for a2 from column J that is for a2 matching the name in b1:H1

Some notes
Column K is in the format Hours, Minutes, Seconds
The cell to the right of the individuals name in column J is always blank
Not all individuals have the same number of events as there peers
Information pertaining to a individual is only from the person name to the following blank spot in column K - for example the only events that pertain for A2(john) are from J3:J6

a

b

c

d

e

f

g

h

i

j

k

l

1

Name

No Code

Break

Lunch

Forced Rel

Other

Meeting

OOS

State Name

Duration

2

John

John

3

Jacob

Break

0:15:48

4

Anthony

Lunch

0:04:02

5

Kyle

Other

0:00:16

6

Steven

Break

0:32:13

7

Tina

Jacob

8

Sara

Break

0:14:33

9

Luke

Break

0:15:05

10

Jennifer

OOS

0:07:01

11

Kyle

12

Break

0:15:00

13

Break

0:13:00

14

Break

0:20:25

15

No code

1:03:01

16

Other

0:47:05

17

Steven

18

OOS

0:03:40

19

Forced Rel

0:00:15

20

Meeting

1:00:41

21

Jennifer

22

Lunch

6:00:00

23

Luke

24

Break

0:33:11

25

No code

0:00:10

26

Meeting

0:45:00

27

28

29

30

31

32

33

34

35

36

37

38

39

40

<tbody>
</tbody>

 

[Eric W]

Put this formula in B2, and copy down and across as needed.

=IFERROR(1/(1/SUMPRODUCT($K$2:$K$50,--($J$2:$J$50=B$1),--(ROW($J$2:$J$50)-ROW($J$2)+1>MATCH($A2,$J$2:$J$50,0)),--(ROW($J$2:$J$50)-ROW($J$2)+1< AGGREGATE(15,6,ROW($K$2:$K$50)/((ROW($K$2:$K$50)-ROW($J$2)+1>MATCH($A2,$J$2:$J$50,0))*($K$2:$K$50="")),1)))),"")<aggregate(15,6,row($k$2:$k$50) ((row($k$2:$k$50)-row($j$2)+1=""><aggregate(15,6,row($k$2:$k$50) ((row($k$2:$k$50)="">

</aggregate(15,6,row($k$2:$k$50)></aggregate(15,6,row($k$2:$k$50)>