Excel Formula to count no. activities across number of days and time. Array formula?

[nvdunn]

HI there
I am tracking the daily activities of staff to see where they are spending the majority of their time and where their pain points are. I have a tab full of data that has been entered in intervals and i want to be able to count the differerent users activities across the whole time period and summarise how much time they are spending in each activities.

For example, i have 4 users being tracked across 4 days for 8 hours. example below:

[TABLE="width: 1440"]
<colgroup><col><col><col><col><col span="4"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]8am[/TD]
[TD]9am[/TD]
[TD]10am [/TD]
[TD]11am[/TD]
[TD]12pm[/TD]
[TD]1pm[/TD]
[TD]2PM[/TD]
[TD]3pm[/TD]
[TD]4pm[/TD]
[TD]5pm[/TD]
[/TR]
[TR]
[TD="align: right"]20/06/2016[/TD]
[TD]John Smith[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting [/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting [/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[/TR]
[TR]
[TD="align: right"]20/06/2016[/TD]
[TD]Candy Lane[/TD]
[TD]Emails[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD="align: right"]20/06/2016[/TD]
[TD]Susan Dry[/TD]
[TD]Board Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[/TR]
[TR]
[TD="align: right"]20/06/2016[/TD]
[TD]Boris Smitten[/TD]
[TD]Client meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD="align: right"]21/06/2016[/TD]
[TD]John Smith[/TD]
[TD]Prepare for meeting[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD="align: right"]21/06/2016[/TD]
[TD]Candy Lane[/TD]
[TD]Phone Call[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[/TR]
[TR]
[TD="align: right"]21/06/2016[/TD]
[TD]Susan Dry[/TD]
[TD]Emails[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD="align: right"]21/06/2016[/TD]
[TD]Boris Smitten[/TD]
[TD]Board Meeting[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD="align: right"]22/06/2016[/TD]
[TD]John Smith[/TD]
[TD]Client meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD="align: right"]22/06/2016[/TD]
[TD]Candy Lane[/TD]
[TD]Prepare for meeting[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting [/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting [/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[/TR]
[TR]
[TD="align: right"]22/06/2016[/TD]
[TD]Susan Dry[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD="align: right"]22/06/2016[/TD]
[TD]Boris Smitten[/TD]
[TD]Emails[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[/TR]
[TR]
[TD="align: right"]23/06/2016[/TD]
[TD]John Smith[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD="align: right"]23/06/2016[/TD]
[TD]Candy Lane[/TD]
[TD]Client meeting[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD="align: right"]23/06/2016[/TD]
[TD]Susan Dry[/TD]
[TD]Prepare for meeting[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting [/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting [/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[/TR]
[TR]
[TD="align: right"]23/06/2016[/TD]
[TD]Boris Smitten[/TD]
[TD]Emails[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
</tbody>[/TABLE]


I would like a nice summary summing the amount of Phone Calls entered for John Smith in total (across all the data entries). I've tried an array formula but it didn't quite work. Can someone please help me. This is the result i'm trying to achieve:

[TABLE="width: 860"]
<colgroup><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Internal Meeting[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Candy Lane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Susan Dry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boris Smitten[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If anyone can please help me it would be very much appreciated.
many thanks

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]8am[/TD]
[TD]9am[/TD]
[TD]10am[/TD]
[TD]11am[/TD]
[TD]12pm[/TD]
[TD]1pm[/TD]
[TD]2PM[/TD]
[TD]3pm[/TD]
[TD]4pm[/TD]
[TD]5pm[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]20/06/2016[/TD]
[TD]John Smith[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting[/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting[/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]20/06/2016[/TD]
[TD]Candy Lane[/TD]
[TD]Emails[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]20/06/2016[/TD]
[TD]Susan Dry[/TD]
[TD]Board Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]20/06/2016[/TD]
[TD]Boris Smitten[/TD]
[TD]Client meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]21/06/2016[/TD]
[TD]John Smith[/TD]
[TD]Prepare for meeting[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]21/06/2016[/TD]
[TD]Candy Lane[/TD]
[TD]Phone Call[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]21/06/2016[/TD]
[TD]Susan Dry[/TD]
[TD]Emails[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]21/06/2016[/TD]
[TD]Boris Smitten[/TD]
[TD]Board Meeting[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]22/06/2016[/TD]
[TD]John Smith[/TD]
[TD]Client meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]22/06/2016[/TD]
[TD]Candy Lane[/TD]
[TD]Prepare for meeting[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting[/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting[/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]22/06/2016[/TD]
[TD]Susan Dry[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]22/06/2016[/TD]
[TD]Boris Smitten[/TD]
[TD]Emails[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]23/06/2016[/TD]
[TD]John Smith[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]23/06/2016[/TD]
[TD]Candy Lane[/TD]
[TD]Client meeting[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Emails[/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]23/06/2016[/TD]
[TD]Susan Dry[/TD]
[TD]Prepare for meeting[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting[/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[TD]Client Meeting[/TD]
[TD]Internal Meeting[/TD]
[TD]Phone Call[/TD]
[TD]Meeting[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]23/06/2016[/TD]
[TD]Boris Smitten[/TD]
[TD]Emails[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD][/TD]
[TD]Phone Call[/TD]
[TD]Emails[/TD]
[TD]Board Meeting[/TD]
[TD]Client meeting[/TD]
[TD]Prepare for meeting[/TD]
[TD]Internal Meeting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]John Smith[/TD]
[TD]

8​

[/TD]
[TD]

6​

[/TD]
[TD]

5​

[/TD]
[TD]

9​

[/TD]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]Candy Lane[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

5​

[/TD]
[TD]

9​

[/TD]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]Susan Dry[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

5​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]Boris Smitten[/TD]
[TD]

5​

[/TD]
[TD]

9​

[/TD]
[TD]

6​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

b23=SUMPRODUCT(($B$2:$B$17=$A23)*($C$2:$L$17=B$22)) copy across and down

 

[nvdunn]

Super Star thank you so much MARZIOTULLIO
Very much appreciated!

 

[MARZIOTULLIO]

Hi

You are welcome