#1
|
|||
|
|||
Sumproduct formula
Hi,
I am really in a big muddle and any help would be very much appreciated. I have a list containing an average of evaluations scores, some of the individuals may have 2 or more units in column A and the Score in Column B is the average of the evaluations. All this is OK but when I want to calculate the Score for the hierarchy it becomes difficult as the list contains more than one manager, so I do need help to build a formula where I can calculate the overall score for Joe Blogs team, the example below: As a result I want to have the score for User ID (147386) and in Week 6 only, if I do a straight SUMPRODUCT as SUMPRODUCT(A2:A7*B2:B7)/SUM(A2:A7) would give me a score of 60.56 great… no issues but my list contains more than 1 user and more than 1 week… Thanks, |
#2
|
|||
|
|||
Thanks guys,
Managed to get the solution... Using excel 2003: =SUM(INDEX((C2:C1000=147386)*(D21000=6)*A2:A1000 *B2:B1000,))/SUMPRODUCT(--(C2:C1000=147386),--(D21000=6),--(A2:A1000)) Using 2007-2010 =SUM(INDEX((C2:C1000=147386)*(D21000=6)*A2:A1000 *B2:B1000,))/SUMIFS(A2:A1000,C2:C1000,147386,D21000,6) Or =SUM(INDEX((C2:C1000=147386)*(D21000=6)*A2:A1000 *B2:B1000,))/SUM(INDEX((C2:C1000=147386)*(D21000=6)*A2:A1000, )) |
#3
|
||||
|
||||
Your formula does not seem to work.
I also do not understand the combination SUM/INDEX you are using
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Match Index with sumproduct/vlookup | angie.chang | Excel | 1 | 06-18-2012 08:47 AM |
Sumproduct | angie.chang | Excel | 3 | 06-14-2012 10:00 AM |
Formula help | furface00 | Excel | 10 | 02-26-2012 05:34 AM |
sumproduct formula to pull info from multiple sheets | Berk21 | Excel | 7 | 01-15-2012 11:41 AM |
help with a look up or if formula | beb1227 | Excel | 3 | 12-31-2011 01:08 PM |