Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct syntax question
This is my first attempt a using sumproduct and testing by rows and columns
together. My goal is to: Sum all cells U10:DL30 if Row 7 <= the current month if Row 9 = "projected" if C10:C30 = B31 I have tested all 4 arrays of the following formula individually and they produce the correct value, but when I combine the 4 together as the following I do not get the desired result: {=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0))--($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$ 30=$B31))} I've tried switching the order of the arrays and I get different results, but never the correct result. Thanks for reading. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct syntax question
Hi
try =SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0)),--($U$9:$DL$9="Projected"),--($C$10:$C$30=$B31),($U$10:$DL$30)) "ocuhcs" wrote: This is my first attempt a using sumproduct and testing by rows and columns together. My goal is to: Sum all cells U10:DL30 if Row 7 <= the current month if Row 9 = "projected" if C10:C30 = B31 I have tested all 4 arrays of the following formula individually and they produce the correct value, but when I combine the 4 together as the following I do not get the desired result: {=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0))--($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$ 30=$B31))} I've tried switching the order of the arrays and I get different results, but never the correct result. Thanks for reading. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct syntax question
Assuming you copy-and-pasted the formula into your message -- which you
should always do, especially when you have a syntax question -- your mistake is a missing comma before the second "--". There are many ways you can write the SUMPRODUCT. For example: =SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$9="Projected")*($U$10:$DL$30)*($C$10:$C$30=$B31) ) (Does that formula really need to be entered as an array formula, as it appears you had done?) Note that you need "--" before conditional expressions only if they are not used otherwise in an arithmetic expression. For example, the following is equivalent: =SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0)),--($U$9:$DL$9="Projected"),$U$10:$DL$30,--($C$10:$C$30=$B31)) Arguably that form (although I would reorder the last two terms; a personal style preference) is more robust because it avoids #VALUE errors when some of the cells in the range U10:DL30 are text. To that end, I would write: =SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$9="Projected")*($C$10:$C$30=$B31),$U$10:$DL$30) ----- original message ----- "ocuhcs" wrote in message ... This is my first attempt a using sumproduct and testing by rows and columns together. My goal is to: Sum all cells U10:DL30 if Row 7 <= the current month if Row 9 = "projected" if C10:C30 = B31 I have tested all 4 arrays of the following formula individually and they produce the correct value, but when I combine the 4 together as the following I do not get the desired result: {=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0))--($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$ 30=$B31))} I've tried switching the order of the arrays and I get different results, but never the correct result. Thanks for reading. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct syntax question
Try this:
=SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$9="Projected")*($C$10:$C$30=$B31)*$U$10:$DL$30) -- Biff Microsoft Excel MVP "ocuhcs" wrote in message ... This is my first attempt a using sumproduct and testing by rows and columns together. My goal is to: Sum all cells U10:DL30 if Row 7 <= the current month if Row 9 = "projected" if C10:C30 = B31 I have tested all 4 arrays of the following formula individually and they produce the correct value, but when I combine the 4 together as the following I do not get the desired result: {=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0))--($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$ 30=$B31))} I've tried switching the order of the arrays and I get different results, but never the correct result. Thanks for reading. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct syntax question
Just a point of interest. SUMPRODUCT does not need to be entered with
CTRL+SHIFT+ENTER. Just enter as normal formula. "ocuhcs" wrote: This is my first attempt a using sumproduct and testing by rows and columns together. My goal is to: Sum all cells U10:DL30 if Row 7 <= the current month if Row 9 = "projected" if C10:C30 = B31 I have tested all 4 arrays of the following formula individually and they produce the correct value, but when I combine the 4 together as the following I do not get the desired result: {=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0))--($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$ 30=$B31))} I've tried switching the order of the arrays and I get different results, but never the correct result. Thanks for reading. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct syntax question
All of the above comments were solutions - thanks for the insight.
"Sean Timmons" wrote: Just a point of interest. SUMPRODUCT does not need to be entered with CTRL+SHIFT+ENTER. Just enter as normal formula. "ocuhcs" wrote: This is my first attempt a using sumproduct and testing by rows and columns together. My goal is to: Sum all cells U10:DL30 if Row 7 <= the current month if Row 9 = "projected" if C10:C30 = B31 I have tested all 4 arrays of the following formula individually and they produce the correct value, but when I combine the 4 together as the following I do not get the desired result: {=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0))--($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$ 30=$B31))} I've tried switching the order of the arrays and I get different results, but never the correct result. Thanks for reading. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct syntax question
Errata....
I wrote 3 alternative formulations: =SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$9="Projected")*($U$10:$DL$30)*($C$10:$C$30=$B31) ) [....] =SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0)),--($U$9:$DL$9="Projected"),$U$10:$DL$30,--($C$10:$C$30=$B31)) [....] =SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$9="Projected")*($C$10:$C$30=$B31),$U$10:$DL$30) Note that the second form does not work in this particular case because you combine row and column ranges (U:DL and 10:30). My intent in showing the second form was primarily to demonstrate when "--" is needed before a conditional expression, namely when it is an individual parameter. ----- original message ----- "JoeU2004" wrote in message ... Assuming you copy-and-pasted the formula into your message -- which you should always do, especially when you have a syntax question -- your mistake is a missing comma before the second "--". There are many ways you can write the SUMPRODUCT. For example: =SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$9="Projected")*($U$10:$DL$30)*($C$10:$C$30=$B31) ) (Does that formula really need to be entered as an array formula, as it appears you had done?) Note that you need "--" before conditional expressions only if they are not used otherwise in an arithmetic expression. For example, the following is equivalent: =SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0)),--($U$9:$DL$9="Projected"),$U$10:$DL$30,--($C$10:$C$30=$B31)) Arguably that form (although I would reorder the last two terms; a personal style preference) is more robust because it avoids #VALUE errors when some of the cells in the range U10:DL30 are text. To that end, I would write: =SUMPRODUCT(($U$7:$DL$7<=EOMONTH($B$1,0))*($U$9:$D L$9="Projected")*($C$10:$C$30=$B31),$U$10:$DL$30) ----- original message ----- "ocuhcs" wrote in message ... This is my first attempt a using sumproduct and testing by rows and columns together. My goal is to: Sum all cells U10:DL30 if Row 7 <= the current month if Row 9 = "projected" if C10:C30 = B31 I have tested all 4 arrays of the following formula individually and they produce the correct value, but when I combine the 4 together as the following I do not get the desired result: {=SUMPRODUCT(--($U$7:$DL$7<=EOMONTH($B$1,0))--($U$9:$DL$9="Projected")*($U$10:$DL$30)*($C$10:$C$ 30=$B31))} I've tried switching the order of the arrays and I get different results, but never the correct result. Thanks for reading. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax question | Excel Worksheet Functions | |||
SUMPRODUCT use of --() syntax | Excel Worksheet Functions | |||
Sumproduct syntax when using AND, OR | Excel Worksheet Functions | |||
Sumproduct syntax - more complex | Excel Discussion (Misc queries) | |||
syntax question | Excel Worksheet Functions |