Microsoft Excel 2003 (full product)

I need to pull the total sum of surveys per Supervisor (2 supv): Supervisor Employ Survey Total

Smtih John A. 3

Black John B. 10

Black John C. 2

etc...

I need to find out the total number of surveys per Supv. As the above shows, Black has 12 and Smith has 3. I've tried several variations of arrays and I can't seem to get it.Thanks,

TC

Your data is a bit confusing, you state that Black has 12 and Smith has 3but you have listed:Smtih John A. 3

Black John B. 10

Black John C. 2So should it not be that John B Black has 10 and John C Black has 2?

Assuming that the names are in column A and totals are in column B

You could use a =SUMIF formulaIn Column C enter the name of all your personnel one per line,

then in column D enter the formula:=SUMIF($A$1:$A$50,C1,$B$1:$B$50)

Then drag the formula down.

I’ve allowed for 50 rows of data, modify to suit your needs.

MIKE

He said "Survey" Maybe John Black got 10 ratings of B and 2 ratings of C for a total of 12 ratings, or 12 survey

results.Just guessing...

Hi,

This is my take on it:If this is your data, and the Supervisor names are in cells A6 and A7,

A B C 1 Super Employ Surveys 2 Smith John A. 3 3 Black John B. 10 4 Black John C. 2 5 6 Smith 3 7 Black 12Use this formula in B6,

=SUMIF($A$2:$A$4,A6,$C$2:$C$4)

then drag it down to B7.Regards

Now, come on Humar. What are the odds that a company would have 2 out 3 supervisors named "Black" and

allof their subordinates are named "John"?:-)

Hi DerbyDad03, Obviously a Family firm.

:-)

Mike, I think you hit the nail on the head - or perhaps made the nail !

Thanks for the responses, but I still can't get it to work. I failed to mention that I am calculating the data from another worksheet (tab). I'll try to explain again: SUMMARY BY TEAM

Supv. # Team # + Surveys

Mike K. 14 ?

Jon C. 13 ?

Joe M. 4 ?

Grand Total ??On the other worksheet, named Surveys, the data is as follows:

Tech Name Supervisor # + Surveys

RICHARD Jon C. 0

CHARLES Mike K. 0

JOSEPH Jon C. 5

MORGAN Mike K. 0

ANDREW Jon C. 0

STARR,DAVID Jon C. 5

MICHAEL Joe M. 3

LARRY Joe M. 4

JUSTIN Joe M. 2

This continues down to row 99. The idea is to change this worksheet with the monthly data each month and summarize it on another worksheet, named Summary.=sum(Survey!$C$2:$C$99,etc... not sure what to add to get this summarized.

Thanks!

Hi, I still find this confusing.

Can you post your data using the <pre> and </pre> tags, you can enter a set of these tags using the icon at the top of the message box.

This enables headings and data to line-up.

In your most recent post you have:

SUMMARY BY TEAMSupv. # Team # + Surveys

Mike K. 14 ?You start with Supv. # which usually means Supv.

number

but there is no number in the first column.Is Mike K. the supervisor's name

Is 14 the team number

What does + Surveys mean.

What does the ? representIn the second part of your post are the names in UPPER CASE the names of the Techs.

Is there any significance to STARR,DAVID, as it is different to the others.IF ... your table of data on the worksheet named "Surveys" is this:

A B C 1 Tech name Supervisor Surveys 2 RICHARD Jon C. 0 3 CHARLES Mike K. 0 4 JOSEPH Jon C. 5 5 MORGAN Mike K. 0 6 ANDREW Jon C. 0 7 STARR,DAVID Jon C. 5 8 MICHAEL Joe M. 3 9 LARRY Joe M. 4 10 JUSTIN Joe M. 2then IF ... you have this list of Supervisor names on a worksheet named "Summary"

A 1 Supervisors 2 Mike K. 3 Jon C. 4 Joe M.

enter this formula in cell B2=SUMIF(Surveys!$B$2:$B$10,A2,Surveys!$C$2:$C$10)Note the $ signsNow drag this formula down to cell B4 where it will look like this:

=SUMIF(Surveys!$B$2:$B$10,A4,Surveys!$C$2:$C$10)

Enter this in cell B5:=SUM(B2:B4)You will get this:

A B 1 Supervisors Surveys 2 Mike K. 0 3 Jon C. 10 4 Joe M. 9 5 Grand Total 19Regards

Ask Your Question

Weekly Poll

Do you agree with Google's decision to shut down Google+?

Discuss in The Lounge

Poll History