Worksheet Function

[GRIGBY]

Hi,

I have a worksheet that contains the following columns:
Col/ Column Name
A / Date
B / Technician
C / Client
D / Cost Center
E / Ticket Number
F / Device Code
G / MFG
H / Model
I / Serial Number
J / Time
K / Comment

In the Device Code column the code PD and PL are listed.
I need to count the total number of times PD appears for
a specific technician in column B. This same formula is
then used to calculate for the code PL and any other
remaining codes. For instance:

Name / Total Calls / Total PD / Total PL .....
Bob Jones / 30 / 12 / 18
John Smith/10 / 8 / 2

The summarry above is located at the top of the worksheet
and is used to create a maeter summary of the information
from all the worksheets.

I have the total count information for the total number
of tickets and the time calculated, but I have not been
able to get the calculations for the other two items.

Any help on creating this formula would be great and very
appreciated.

Thanks,
Greg

 

[Paul Corrado]

Greg,

This formula will return the string you are looking for. I believe I have
gotten the column references correct, though you will probably need to
adjust the row references. Also, if you have a list of names that you can
reference, you can replace the references to "Bob Jones" with a reference to
those cells.


="Bob Jones / "&SUMPRODUCT((B1:B10="Bob Jones")*(F1:F10="PD"))&" /
"&SUMPRODUCT((B1:B10="Bob Jones")*(F1:F10="PL"))

PC