Attempting to sum criteria in multiple columns

V

vahju

Here is the scenario. I am incharge of creating a spreadsheet that wil
calculate the first call resolution rate of our helpdesk. The firs
call resolution rate means the agent opens a ticket and solves the cal
without routing the issue to another support person or group.

The data is extracted into a excel spreadsheet. I have about 200 plu
rows and 15 columns. The only three columns that are used to determin
first call resolution is CreatedBy, ClosedBy, and RouteCount. On
seperate tab I have a list of all the helpdesk agents.

Raw data is on the data sheet and the Helpdesk agent list is on th
Tech sheet.

Data Sheet:
CreatedBy ClosedBy RouteCount
John Mary 2
Mary John 1
Steve Steve 0
Helen Helen 0

Tech Sheet:
John
Steve
Helen

Here is the logic I believe that should be behind the formula:
If createdby is equal to closedby and routecount is equal to zero the
determine first call resolution.

I tried countif and that gives me the number of tickets opened an
closed in two different cells. Which is fine for the other data
need.

=CountIF(createdby,Tech!A2) "tickets opened by an agent"
=CountIF(closedby,Tech!A2) "tickets closed by an agent"

But First Call Resolution is what I am gunning for.

I even tried using the SUMPRODUCT but keep getting errors.
=SUMPRODUCT(--(Data!C21:C550=B2)*--(Data!X2:X550=Techs!B2))

I pulled the SUMPRODUCT from a previous thread.

I am familar with Excel and simple formulas so any assistance would b
most appreciated.

I forgot to mention that I am using Excel 2002.

Thanks ahead of time
 
F

Frank Kabel

Hi
try something like
=SUMPRODUCT(--(Data!A21:A550="John"),--(Data!A21:A550=Data!B21:B550),--
(Data!C21:C550=0))
 
V

vahju

Thanks Frank that worked like a charm. I substituted the name in quote
to a cell reference and its doing a bang up job.

First post submitted and solved within on hour. I shall be coming her
more often
 
Top