Sumproduct formula

Portucale · #1 09-12-2012, 04:47 AM

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,

Portucale · #2 09-12-2012, 07:45 AM

Thanks guys,

Managed to get the solution...

Using excel 2003:

=SUM(INDEX((C2:C1000=147386)*(D2

1000=6)*A2:A1000 *B2:B1000,))/SUMPRODUCT(--(C2:C1000=147386),--(D2

1000=6),--(A2:A1000))

Using 2007-2010

=SUM(INDEX((C2:C1000=147386)*(D2

1000=6)*A2:A1000 *B2:B1000,))/SUMIFS(A2:A1000,C2:C1000,147386,D2

1000,6)

Or

=SUM(INDEX((C2:C1000=147386)*(D2

1000=6)*A2:A1000 *B2:B1000,))/SUM(INDEX((C2:C1000=147386)*(D2

1000=6)*A2:A1000, ))

Pecoflyer · #3 09-12-2012, 10:51 AM

Your formula does not seem to work.
I also do not understand the combination SUM/INDEX you are using

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