Count Ig Function

hey yer barred

Board Regular
Joined
Jun 19, 2007
Messages
232
Hi All!

I have a list of names and what type of call they have done.

in total there is 7 different people and 2 types of call types, inbound and outbound.

I want a table that will show the Agents name and how many inbound and how many outbound calls that agent has done.

So I want 3 coumns, name, inbound calls, outbound calls.

I have tried using countif's and pivots but havnt got very far!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
Column A1 = Agent's Name
Column B1 = Type of call

Column E1 = Agent's Name
Column F1 = inbound
Column G1 = outbound

Column F2 =SUMPRODUCT(--($A$1:$A$12=E2),--($B$1:$B$12=$F$1))
Column G2 =SUMPRODUCT(--($A$1:$A$12=E2),--($B$1:$B$12=$G$1))

Copy and paste for all agent's name.
 

Dylan

Board Regular
Joined
Jul 27, 2007
Messages
150
Table 1
Column A1 = Agent's Name
Column B1 = Type of call

Table 2
Column E1 = Agent's Name
Column F1 = inbound
Column G1 = outbound

Column F2 =SUMPRODUCT(--($A$1:$A$12=E2),--($B$1:$B$12=$F$1))
Column G2 =SUMPRODUCT(--($A$1:$A$12=E2),--($B$1:$B$12=$G$1))

Copy and paste for all agent's name.
 

hey yer barred

Board Regular
Joined
Jun 19, 2007
Messages
232

ADVERTISEMENT

Ant ive just realised why...its doing a sum...In the call type column, it says Outbound or Inbound...so I need a count formula..not a sum sorry!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
How have you tried pivot tables?

Sounds to me as though it would be pretty straightforward.
 

hey yer barred

Board Regular
Joined
Jun 19, 2007
Messages
232

ADVERTISEMENT

You know in the caculated field function in a pivot table...can you put a count if in the formula bar...I have and it isnt working!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Why do you want/think you need a countif?

You've not given much detail about your data structure but it could be just a case of dragging the right field to the right part of the pivot table.
 

hey yer barred

Board Regular
Joined
Jun 19, 2007
Messages
232
I Have 2 coumns, Agent, Call Type

Agent has a list of agnets and call type has what type of call they did, inbound or outbound. Looks like this

Agent | Call Type
Tim | Inbound
Barry | Inbound
Tim | Outbound
Barry | Inbound
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Well I've just created a pivot table from that data.

I put Agent as a row item, Call Type as a column item and Count of Call Type as a data item.

Seems to give the correct results.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top