Sum Product Question

K

kcaenj

I have a spreadsheet with the following columns.

Business Group (1, 2, 3)
Employee Status (ACTIVE, TERM)
Performance Score (1 through 9)
Potential Score (1 through 4)

I need to figure out the average performance and potential scores fo
every cominbation of Business Group and Status.

What is the simplest way using SUMPRODUCT to do this? I know will nee
mulitple formulas to get this done but I am at a loss particualry ho
to count the scores up for a total as some of the data is missing and
don't want to count ones where data is missing.

Thanks,

Ada
 
B

Bob Phillips

=SUMPRODUCT(--(Business_Group=1),--(Employee_Status="ACTIVE"),--(Performance
_Score))

etc.

You could put the values in cells and refer to these.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

Aladin Akyurek

=AVERAGE(IF((GroupRange=1)*(StatusRange="ACTIVE")*PerformanceScroreRange,Per
formanceScroreRange))

which must be confirmed with control+shift+enter instead of just with enter.

Note that the ranges must be of equal sizes and whole columns are not
allowed as reference.
 
Top