Advice needed on Count/IF function

writinglife

New Member
Joined
Sep 9, 2014
Messages
4
I've skimmed the forum and can't find a close resemblance to my issue. If I've missed somethig, please let me know.

I have to provide a report of how many service calls were logged by type. A report that is automatically produced provides me with 1 column that shows the source (phone/email). In my report (an excel spreadsheet that takes information from the raw data provided and has the names of the employees listed already. What I need to to is take the phone/email data and report how many phone calls and emails that person A-Z took.

At the moment, I just use a Count function to tell me how many times the name appears in the "logged" column, but this needs to change to report how many times this person appeared in the logged column when the source column read "phone" or "email".

I probably haven't explained it well, but I hope someone here can help.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
No problem,

Desired outcome is

[TABLE="width: 235"]
<TBODY>[TR]
[TD]Analyst</SPAN>[/TD]
[TD]Logged (Phone)</SPAN>[/TD]
[TD]Logged (Email)</SPAN>[/TD]
[/TR]
[TR]
[TD]Employee 1</SPAN>


The raw data is Col K for Opened By and Col L for Incident Source
[TABLE="width: 194"]
<COLGROUP><COL style="WIDTH: 97pt" span=2 width=129><TBODY>[TR]
[TD="class: xl65, width: 129, bgcolor: lightgrey"]Opened By[/TD]
[TD="class: xl65, width: 129, bgcolor: lightgrey"]Incident Source[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 1[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 2[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 3[/TD]
[TD="class: xl66, bgcolor: transparent"]Email[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 4[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 5[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 6[/TD]
[TD="class: xl66, bgcolor: transparent"]Email[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 7[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 8[/TD]
[TD="class: xl66, bgcolor: transparent"]Email[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 9[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 10[/TD]
[TD="class: xl66, bgcolor: transparent"]Email[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 11[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 12[/TD]
[TD="class: xl66, bgcolor: transparent"]Email[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 13[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 14[/TD]
[TD="class: xl66, bgcolor: transparent"]Email[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 15[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 16[/TD]
[TD="class: xl66, bgcolor: transparent"]Email[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 17[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 18[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 19[/TD]
[TD="class: xl66, bgcolor: transparent"]Phone[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Employee 20[/TD]
[TD="class: xl66, bgcolor: transparent"]Email[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=2></COLGROUP>[/TABLE]
 
Upvote 0
Change Logged (Phone) to just Phone and the same for the next entry.

Let N1:P2 house the corrected upper part...

O2, copied across:

=COUNTIFS(K:K,$N2,L:L,O$1)
 
Upvote 0
Aladin, thanks for the reply, I appreciate the assistance.

Unfortunately, I'm not as Excel literate as you and you've confused me a little. Firstly, I must apologise, the raw data snippet I've provided is just a couple of columns that are seperated in the raw data I received. A more accurate representation would have the "opened by" as Column K and the source as column AI. It's a very big source document that we ilter in a lot of different ways to obtain info. I'm looking to keep the process as smple as possible by dumping the raw data into one worksheet and letting the output sheet do the work. So in this case it's saying if Column AI shows phone, then count how many times Employee 1 appears.

I'd very much appreciate an idiots explanation.
 
Upvote 0
aladin, thanks for the reply, i appreciate the assistance.

Unfortunately, i'm not as excel literate as you and you've confused me a little. Firstly, i must apologise, the raw data snippet i've provided is just a couple of columns that are seperated in the raw data i received. A more accurate representation would have the "opened by" as column k and the source as column ai. It's a very big source document that we ilter in a lot of different ways to obtain info. I'm looking to keep the process as smple as possible by dumping the raw data into one worksheet and letting the output sheet do the work. So in this case it's saying if column ai shows phone, then count how many times employee 1 appears.

I'd very much appreciate an idiots explanation.

You seem to have the "Where Am I?" problem on a map that is mounted on a pole in a city entrance;)... Let's try a different map:

=COUNTIFS(K:K,"Employee 1",AI:A1,"Phone")

Does this help?
 
Upvote 0
Aladin,

It does indeed! A little tweaking got it working perfectly.

Thanks VERY much for your help, It's very gratefully appreciated.
 
Upvote 0

Forum statistics

Threads
1,223,152
Messages
6,170,389
Members
452,324
Latest member
stuart1980

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