Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Sumproduct formula Sumproduct formula
Register FAQ Mark Forums Read

Reply
 
Thread Tools Display Modes
  #1  
Old 09-12-2012, 04:47 AM
Portucale Portucale is offline Sumproduct formula Windows 7 32bit Sumproduct formula Office 2010 32bit
Novice
Sumproduct formula 		 
Join Date: Sep 2012
Posts: 3
Portucale is on a distinguished road
Default 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,
Attached Files
File Type: xlsx Book2.xlsx (9.7 KB, 10 views)
Reply With Quote
  #2  
Old 09-12-2012, 07:45 AM
Portucale Portucale is offline Sumproduct formula Windows 7 32bit Sumproduct formula Office 2010 32bit
Novice
Sumproduct formula 		 
Join Date: Sep 2012
Posts: 3
Portucale is on a distinguished road
Default
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, ))
Reply With Quote
  #3  
Old 09-12-2012, 10:51 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Sumproduct formula Windows 7 64bit Sumproduct formula Office 2010 64bit
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,915
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default
Your formula does not seem to work.
I also do not understand the combination SUM/INDEX you are using
__________________
Using O365 v2503 - 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
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode


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 formula Sumproduct angie.chang Excel 3 06-14-2012 10:00 AM
Formula help furface00 Excel 10 02-26-2012 05:34 AM
Sumproduct formula 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:54 AM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft