Advice needed on Count/IF function

[writinglife]

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.

 

[Aladin Akyurek]

Would you post a small sample along with the desired result based on that sample.

 

[writinglife]

No problem,

Desired outcome is

Analyst</SPAN>	Logged (Phone)</SPAN>	Logged (Email)</SPAN>
Employee 1</SPAN> The raw data is Col K for Opened By and Col L for Incident Source Opened By Incident Source Employee 1 Phone Employee 2 Phone Employee 3 Email Employee 4 Phone Employee 5 Phone Employee 6 Email Employee 7 Phone Employee 8 Email Employee 9 Phone Employee 10 Email Employee 11 Phone Employee 12 Email Employee 13 Phone Employee 14 Email Employee 15 Phone Employee 16 Email Employee 17 Phone Employee 18 Phone Employee 19 Phone Employee 20 Email <COLGROUP><COL style="WIDTH: 97pt" span=2 width=129><TBODY> </TBODY>

<TBODY>
</TBODY><COLGROUP><COL><COL span=2></COLGROUP>

 

[Aladin Akyurek]

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)

 

[writinglife]

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.

 

[Aladin Akyurek]

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?

 

[writinglife]

Aladin,

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

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

 

[Aladin Akyurek]

Aladin,

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

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

You are welcome.