Count a number if a set word/words is in a cell

[Paul365]

Hello,

I am trying to create a easy formula which can count hours for staff on a schedule automatically, however some names are going to be in the same cell.

Example:

A B C D E F
A1 Start Time Finish Time Hours Venue Staff Name Hours Worked
A2 8:00 am 10:00 am 2 Paul, Taylor Paul 4
A3 12:00 pm 2:00 pm 2 Paul Taylor 2

 

[StephenCrump]

Take 1 below is the simplest way to do this, but there is a risk of incorrect results if one name can be found in another, e.g. Pam in Pamela, or John in John-Paul.

Take 2 is safer, and relies on wrapping each name in a special character - people often use "|" here, so that there is a difference between |Pam| and |Pamela|.

I've used Char(10) on the assumption that your data is using Char(10) to separate multiple names in the same cell?

	A	B	C	D
1	Amount	Person		Person
2	5	Taylor Paul		Pamela Paul
3	7	Taylor		Pamela
4	11	Bob Taylor		Pam Pamela
5
6	Take 1
7	Paul	5	Paul	5
8	Taylor	23	Pam	23
9	Bob	11	Pamela	23
10
11	Take 2
12	Paul	5	Paul	5
13	Taylor	23	Pam	11
14	Bob	11	Pamela	23
Sheet1

Cell Formulas
Range	Formula
B7:B9,B12:B14,D7:D9	B7	=SUMPRODUCT($A$2:$A$4,--ISNUMBER(SEARCH(A7,B$2:B$4)))
D12:D14	D12	=SUMPRODUCT($A$2:$A$4,--ISNUMBER(SEARCH(CHAR(10)&C12&CHAR(10),CHAR(10)&D$2:D$4&CHAR(10))))