Average(If...

M

MD

Please Help...This is a Quality Monitoring Form.

Sheet B has all representatives sorted Alphanumerically.
They all report to different teams. My formula resides on
sheet A and reads {=AVERAGE(IF('Sheet B'!$D$2:$D$200="Team
1",'Sheet B'!$F$2:$F$200))}

The "D" column represents the Team Number and the "F"
column represents the score that I want retrieved.

I can't sort by Team, because they change often. The
reason I can't change the sheets is because each of the
sheets represent a week in the month with the same
Representatives.

If this is too unclear, please contact me for
clarification.

Thank you.
 
J

Jon Peltier

I'm not sure exactly what you're asking for, but I'll describe how I do this, and
you might get an idea.

If your items in column D change frequently, your formulas will soon become
obsolete. I like to use the region below the table to set out the items I want to
sum by. I'll just show my summaries below columns D and F:

Row Column D Column F
--- -------- --------
201 Team 1 {=AVERAGE(IF($D$2:$D$200=$D201,F$2:F$200))}
202 Team 2 {=AVERAGE(IF($D$2:$D$200=$D202,F$2:F$200))}

I can easily get my totals for Team 1, Team 2, etc., and if I'm smart writing the
formula, I can write it once and drag or copy-paste it to use it in more cells. I
took the $ signs off the references to column F, in case I also want to total up
column G.

If columns B and C list other attributes, you can expand this to add by any single
column, or any combination of columns.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Top