grouping excel

I have two column one has program manager and one has event personal.

one program manager may have 14 programs and may have 50 event part of the programs. some of the event , the program manager maybe the event personal and it maybe some one different. What I have to calculate is the percentage of each program by whom it was handeled by event personal.

ProgramName     program manager     personal name  date
Hilton                   Smith                         Matt
Hilton                    Smith                         Smith
Hilton                    Smith                         Eddy
Starbucks             Smith                        smith
Starbucks            smith                         Matt
Starbucks            smith                         Matt
Starbucks            smith                         smith  
Starbucks            smith                         George
Starbucks            smith                         Matt

what I would like to calculate is that how many time smith was the personal for program that he was the manager for and how many time in total was some one else.
result will be out of the six time, smith managed 2 and 4 times others.

I am about to loose my mind. I have hundreds of rcords like this for many managers and many programs.

starbucks    smith  
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Is this in Access or Excel?

Given the data above, please specify EXACTLY what you would expect the result to be.
Assuming the answer you are looking for is "SMITH | 2 | 4", here it goes, in Access, with source table named "tblSource":

SELECT tblSource.[ProgramName], tblSource.[ProgramManager], tblSource.[PersonalName]
FROM tblSource
GROUP BY tblSource.[ProgramName], tblSource.[ProgramManager], tblSource.[PersonalName];

SELECT qry1RemoveDuplicates.ProgramName, qry1RemoveDuplicates.ProgramManager, qry1RemoveDuplicates.PersonalName, IIf([Programmanager]=[PersonalName],1,0) AS CountSelf, IIf([Programmanager]<>[PersonalName],1,0) AS CountOther
FROM qry1RemoveDuplicates;


SELECT [qry2CountSelf&Other].ProgramManager, Sum([qry2CountSelf&Other].CountSelf) AS SumOfCountSelf, Sum([qry2CountSelf&Other].CountOther) AS SumOfCountOther
FROM [qry2CountSelf&Other]
GROUP BY [qry2CountSelf&Other].ProgramManager;
Rob HensonFinance AnalystCommented:
I suspect you can do this with a couple of simple countif statements.

Event/program  col A
Manager            col B
Personnel          col C

Reference cell with Event, cell with Manager name, eg A15 & B15, formula in say D15 & E15:

D15  =COUNTIFS(A1:A12,A15,B1:B12,B15) would count total number of occurences for Manager and Event
E15  =COUNTIFS(A1:A12,A15,C1:C12,B15) would count total number of occurences for Personnel and Event

Therefore, first count minus second count would give number of Manager and not Personnel occurences.

I would generate an example for you but working on xl2003 at the minute which does not have COUNTIFS function.

Rob H

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.