Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Help
I am having a problem using Sumproduct. The sumproduct formula is gathering the necessary information using: =SUMPRODUCT((($M93='2007 Rems Slide Obl'!$C$4:$C$1918)*('Branches 2007 Budget'!$J93='2007 Rems Slide Obl'!$U$4:$U$1918)*('2007 Rems Slide Obl'!W$4:W$1918))) But I am trying to multiply the results from above by the information information in the adjacent cell P93 which is a percentage. I was hoping that by doing: =SUMPRODUCT((($M93='2007 Rems Slide Obl'!$C$4:$C$1918)*('Branches 2007 Budget'!$J93='2007 Rems Slide Obl'!$U$4:$U$1918)*('2007 Rems Slide Obl'!W$4:W$1918)))*P93 would give me the results but I receive #Value! The information in P93 is a percentage gathered from another tab using: =IF(ISERROR(VLOOKUP($M85,REMS2007!$M$4:$P$1786,4,F ALSE)),0,VLOOKUP($M85,REMS2007!$M$4:$P$1786,4,FALS E)) Is there a way that I can multiply the result in Q83 by P83 to get the percentage. I know that it works by doing copy, paste, special and listing the end results of the percentage. -- Merlin54k ------------------------------------------------------------------------ Merlin54k's Profile: http://www.excelforum.com/member.php...o&userid=35199 View this thread: http://www.excelforum.com/showthread...hreadid=549654 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Help
seems to me u got a text value somwhere in ur referenses
if this 1 is ok =SUMPRODUCT((($M93='2007 Rems Slide Obl'!$C$4:$C$1918)*('Branches 2007 Budget'!$J93='2007 Rems Slide Obl'!$U$4:$U$1918)*('2007 Rems Slide Obl'!W$4:W$1918))) then check if =IF(ISERROR(VLOOKUP($M85,REMS2007!$M$4:$P$1786,4,F ALSE)),0,VLOOKUP($M85,REMS2007!$M$4:$P$1786,4,FALS E)) returns a numeric value |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Help
The Sumproduct gathers the intended information using but the problem is when I add *P83 to the end of it in an effort to mulitply it to the adjacent cell. P83 the adjacent cell produces a number from VLook Up. CELL Q85 Has: =SUMPRODUCT((($M85='2007 Rems Slide Obl'!$C$4:$C$1918)*('Branches 2007 Budget'!$J85='2007 Rems Slide Obl'!$U$4:$U$1918)*('2007 Rems Slide Obl'!W$4:W$1918))) CELL P85 Has (proucing a percentage result): =IF(ISERROR(VLOOKUP($M85,REMS2007!$M$4:$P$1786,4,F ALSE)),0,VLOOKUP($M85,REMS2007!$M$4:$P$1786,4,FALS E)) I was hoping that by doing: =SUMPRODUCT((($M85='2007 Rems Slide Obl'!$C$4:$C$1918)*('Branches 2007 Budget'!$J85='2007 Rems Slide Obl'!$U$4:$U$1918)*('2007 Rems Slide Obl'!W$4:W$1918)))*P85 I would get the result in Q85 pro-rates based on the percentage in P85. -- Merlin54k ------------------------------------------------------------------------ Merlin54k's Profile: http://www.excelforum.com/member.php...o&userid=35199 View this thread: http://www.excelforum.com/showthread...hreadid=549654 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding "OR" to a Sumproduct Formula | Excel Worksheet Functions | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |