Count the number of patients assigned per employee

[srburk]

My employees submit a daily spreadsheet listing each doctor's name, the number of patients scheduled for that doctor, and the name of the employee assisting the provider.

Doctor A 5 Tom
Doctor B 3 Frank
Doctor C 8 Crystal
Doctor D 2 Tom
Doctor E 1 Tom
Doctor F 2 Tom
Doctor G 6 Frank
Doctor H 9 Crystal

I would like for the sheet to automatically calculate the number of patients each employee ends up providing services for. The doctors are only listed once but the employees could be assigned multiple doctors. From the data above, I would like to see a result that shows:

Tom 10
Frank 9
Crystal 17

Such a result would show to me that the work is not spread out evenly.

How would I calcalulate the range to count the sum of patients per employee?

 

[millhouse123]

the sumif function should work. List the names of each employee and then simply
=sumif(C1:C100,F1,B1:B100) where F1 is the name in the list you made

 

[danzon]

use sumif

so for tom....
=SUMIF(C$1:C$20,"tom",B$1:B$20)

adds up column B1 thru B20 where column C = tom

 

[Taul]

or sumproduct

=SUMPRODUCT((C2:C9="Tom")*(B2:B9))

 

[T. Valko]

My employees submit a daily spreadsheet listing each doctor's name, the number of patients scheduled for that doctor, and the name of the employee assisting the provider.

Doctor A 5 Tom
Doctor B 3 Frank
Doctor C 8 Crystal
Doctor D 2 Tom
Doctor E 1 Tom
Doctor F 2 Tom
Doctor G 6 Frank
Doctor H 9 Crystal

I would like for the sheet to automatically calculate the number of patients each employee ends up providing services for. The doctors are only listed once but the employees could be assigned multiple doctors. From the data above, I would like to see a result that shows:

Tom 10
Frank 9
Crystal 17

Such a result would show to me that the work is not spread out evenly.

How would I calcalulate the range to count the sum of patients per employee?

One way...

If you have a list of the unique employee names:

Book1
	A	B	C	D	E	F
2	Doctor A	5	Tom	_	Tom	10
3	Doctor B	3	Frank	_	Frank	9
4	Doctor C	8	Crystal	_	Crystal	17
5	Doctor D	2	Tom	_	_	_
6	Doctor E	1	Tom	_	_	_
7	Doctor F	2	Tom	_	_	_
8	Doctor G	6	Frank	_	_	_
9	Doctor H	9	Crystal	_	_	_
Sheet1

This formula entered in F2 and copied down:

=SUMIF(C$2:C$9,E2,B$2:B$9)