Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct question - kind of...
I needed a formula (that I was able to find on this group, btw). The formula
from the gropu was: =sumproduct(--(B2:B10=1),--(F2:F10="Completed")) My question is, what does the -- do, and can I find more information about this in the help files? If it is in help, what is it called, as I haven't been able to find anything about it - even when looking at the sumproduct help information. Thanks, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct question - kind of...
-- is known as double unary minus, its function in a formula like =sumproduct(--(B2:B10=1),--(F2:F10="Completed")) is to convert an array of TRUE/FALSE values to 1/0, e.g. (B2:B10=1) returns an array like {TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE} -- coerces this to {1,0,1,1,0,0,1,1,1} the same happens with --(F2:F10="Completed") SUMPRODUCT then multiplies the two arrays (giving 1 only when both arrays are 1) and then sums the resultant single array thereby effectively counting the rows where both conditions are true. The arrays can be co-erced in other ways, e.g. by adding zero or multiplying by 1, so you could as easily use =sumproduct((B2:B10=1)+0,(F2:F10="Completed")*1) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=533005 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct question - kind of...
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. It isn't in help. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Poody" wrote in message ... I needed a formula (that I was able to find on this group, btw). The formula from the gropu was: =sumproduct(--(B2:B10=1),--(F2:F10="Completed")) My question is, what does the -- do, and can I find more information about this in the help files? If it is in help, what is it called, as I haven't been able to find anything about it - even when looking at the sumproduct help information. Thanks, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct question - kind of...
Thank you both very much - that is great information.
Rich "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. It isn't in help. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Poody" wrote in message ... I needed a formula (that I was able to find on this group, btw). The formula from the gropu was: =sumproduct(--(B2:B10=1),--(F2:F10="Completed")) My question is, what does the -- do, and can I find more information about this in the help files? If it is in help, what is it called, as I haven't been able to find anything about it - even when looking at the sumproduct help information. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Pivot Table Question - KIND of UrgentTh | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
sumproduct question | Excel Worksheet Functions |