Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct
I have a question about the use of Sumproduct to match multiple
conditions. While the calculations work as expected when I follow the guide at this site: http://www.contextures.on.ca/xlFunct...tml#SumProduct What is the difference between SUMPRODUCT(--(A1:A10=0)) and SUMPRODUCT((A1:A10=0))? I am confused by the -- syntax. I have looked around and can't seem to find any explanation for what it means. If I don't include them in my Sumproduct calculations, the answers are incorrect. What gives? Marc |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct
Give this link a try...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "meckhert" wrote: I have a question about the use of Sumproduct to match multiple conditions. While the calculations work as expected when I follow the guide at this site: http://www.contextures.on.ca/xlFunct...tml#SumProduct What is the difference between SUMPRODUCT(--(A1:A10=0)) and SUMPRODUCT((A1:A10=0))? I am confused by the -- syntax. I have looked around and can't seem to find any explanation for what it means. If I don't include them in my Sumproduct calculations, the answers are incorrect. What gives? Marc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct
(A1:A10=0)
Returns {True,False,True,True,False,True,True,False,True,F alse} But what you want is {1,0,1,1,0,1,1,0,1,0} By using the first negative sign, you converse the Boolean values to numbers {-1,0,-1,-1,0,-1,-1,0,-1,0} Then negating this with the second negative sign, you get what you want {1,0,1,1,0,1,1,0,1,0} You could see this yourself by entering the formula. then highlight (A1:A10=0) in the formula bar and hit F9. Now hit escape to return it to being a formula. Now select -(A1:A10=0) and hit F9, then escape when done looking. Now select --(A1:A10=0) and hit F9, then escape when done looking Using F9 to do an immediate evaluation is often handy when working with array formulas. -- Regards, Tom Ogilvy "meckhert" wrote in message oups.com... I have a question about the use of Sumproduct to match multiple conditions. While the calculations work as expected when I follow the guide at this site: http://www.contextures.on.ca/xlFunct...tml#SumProduct What is the difference between SUMPRODUCT(--(A1:A10=0)) and SUMPRODUCT((A1:A10=0))? I am confused by the -- syntax. I have looked around and can't seem to find any explanation for what it means. If I don't include them in my Sumproduct calculations, the answers are incorrect. What gives? Marc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Sumproduct I think | Excel Worksheet Functions | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct #value! | Excel Worksheet Functions |