Here is my dilemma. I have a list of managers with their representatives they take calls daily and note accounts as they take calls. If calls are not noted or an account can’t be pulled up the account is not noted here is an example of data. Team Manager Name Representative Calls Notes

Manager 1 Rep 1 20 18

Manager 1 Rep 2 55 45

Manager 1 Rep 3 45 40

Manager 2 Rep 4 30 32

Manager 2 Rep 5 15 10

Manager 3 Rep 6 25 18

Manager 3 Rep 7 30 28

Manager 3 Rep 8 75 77

Manager 3 Rep 9 10 8

Manager 3 Rep 10 20 17What I would like to see on sheet two in excel is as follows as of right now all I have is an equation like this to get my average =sum(d2:d5)/sum(c2:c5) in order to get my average for the manager. However, I feel as though there should be a way to just have a list of the manager names like:

Team Manager Team average Noting percentage

Manager 1 90%

Manager 2 95%

Manager 3 97%This would all be on sheet two. I would think such an equation exists however I just haven’t figured it out and any help with this matter would be helpful.

Note the team managers as of right now have to us the formula as I have it now to get their averages and as representatives come and go they are constantly having to change the equation so I am trying to find a streamlined way to where the equation would be self sufficient and I just can't figure it out and I appreciate all the help that anyone can possibly give.

Please allow to start this response by suggesting that you throw in some punctuation every now and then. It would make your posts and questions a little easier to understand. Next, I set up a spreadsheet with your data and modified the example equation you gave (=sum(d2:d5)/sum(c2:c5) to read:

=SUM(D2:D

4)/SUM(C2:C4)This formula returned 85.83% for Manager 1. Please correct me if I've made an incorrect assumption here.

Finally, to get the same 85.83% for Manager 1 without having to know which rows contain Manager 1, I can use this formula:

=SUMIF(A2:A11,A2,D2:D11)/SUMIF(A2:A11,A2,C2:C11)

The Manager List does not have to be sorted like yours is and the list of Manager names that you are searching for can be in another location. You could have a list on Sheet2 and modify the formula to refer to the ranges on Sheet1. See the Help files for SUMIF or come on back if you need an explanation on how SUMIF works.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History