Max number of occurrences based on two variables

[xtort81]

Hello,

I have a "database" that houses statistics for a call center. I have a column with names and then another column with the type of call they took. I want to be able to report on the most common type of call they took by each person. So a formula that singles out their name and essentially counts all the types of calls and then gives me the type that has the most. I've tried different variations of INDEX and MATCH formulas...but I'm not that good with them.

This is what I've tried which works if I want to get the max overall...but it won't give me data for a specific agent.
{=INDEX(D2:D43,MATCH(MAX(COUNTIF(D2:D43,D2:D43)),COUNTIF(D2:D43,D2:D43),0))}

Any help is greatly appreciated.

Thanks.

[loginjmor]

Hi -

This is a perfect application for a pivot table.

[xtort81]

Thanks loginjmor...I've tried using Pivot tables but they don't seem to give me what I need. Well I should say the data is correct but, I'm not sure how to link back to the pivot tables because each person and each month is different.

[loginjmor]

Hi -

I have put together a pivot table based on the sample data you provided and attached it to this post. It has Reasons along the left had side and Names across the top and provides a breakdown summary of reasons by person as well as totals. You can also add in monthly breakdowns, but in this case, the data was all for May. This literally takes about 2 minutes to put together and saves you a TON of coding.

If this isn't what you need, please describe what is missing and I will try to assist further.

Hope this helps.

[xtort81]

Thanks again loginjmor. The data is there...but I need to be able to feed that data over to a scorecard (which I realize I left out of my initial post...sorry). So each month it would show Name 1's top call reason for January was Reason 1 (or whatever it may be), for February it was Reason 2, etc.. I guess my problem is being able to take the data from the pivot table and get it into their scorecard. Would I use an HLOOKUP function...somehow mixed with a MAX function?