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?
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
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;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.