Worksheet Function

  • Thread starter Thread starter GRIGBY
  • Start date Start date
G

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
 
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
 
Back
Top