http://chandoo.org/forums/topic/sumproduct-2 Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Sun, 26 May 2013 03:29:52 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/sumproduct-2#post-15144 Fri, 09 Dec 2011 14:38:43 +0000 Indian 15144@http://chandoo.org/forums/ <p>Thanks.......amazing stuff....You are like a drug..slowly slowly getting in our blood and making us addicted for excel knowledge. </p> http://chandoo.org/forums/topic/sumproduct-2#post-15113 Thu, 08 Dec 2011 15:21:04 +0000 Chandoo 15113@http://chandoo.org/forums/ <p>the -- refers to double negation. The portion (A1:A10=B1) returns only TRUE &amp; FALSE values, where as SUMPRODUCT is meant to work with numbers. So by adding two negative symbols --, you are forcing excel to convert boolean (logical) values to numbers. So TRUE becomes -1, false becomes 0 after the first - symbol. The second symbol, negates and gets you a bunch of 1s &amp; 0s, which the SUMPRODUCT then sums up.</p> <p>Read up this for more: <a href="http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/" rel="nofollow">http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/</a> </p> http://chandoo.org/forums/topic/sumproduct-2#post-15083 Thu, 08 Dec 2011 08:06:49 +0000 NARAYANK991 15083@http://chandoo.org/forums/ <p>Hi ,</p> <p>Any of the following should tell you all that you want to know :</p> <p>1. <a href="http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/" rel="nofollow">http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/</a></p> <p>2. <a href="http://www.exceluser.com/explore/sumproduct_11.htm" rel="nofollow">http://www.exceluser.com/explore/sumproduct_11.htm</a></p> <p>3. <a href="http://www.excel-easy.com/excel-examples/sumproduct.html" rel="nofollow">http://www.excel-easy.com/excel-examples/sumproduct.html</a></p> <p>4. <a href="http://www.excel-examples.com/sumproduct.htm" rel="nofollow">http://www.excel-examples.com/sumproduct.htm</a></p> <p>5. <a href="http://www.tutorialized.com/view/tutorial/SUMPRODUCT/63027" rel="nofollow">http://www.tutorialized.com/view/tutorial/SUMPRODUCT/63027</a></p> <p>6. <a href="http://www.computergaga.com/excel/functions/sumproduct.html" rel="nofollow">http://www.computergaga.com/excel/functions/sumproduct.html</a></p> <p>Narayan </p> http://chandoo.org/forums/topic/sumproduct-2#post-15082 Thu, 08 Dec 2011 07:53:24 +0000 Indian 15082@http://chandoo.org/forums/ <p>Hey Friends.... I some how managed to get the required result by creating an helper column....but can someone explain as to what does the SUMPRODUCT(--( means, I mean how do sumproduct(--(A1:A10=B1)) gives us the count.</p> <p>What does -- mean in the formula......... </p> http://chandoo.org/forums/topic/sumproduct-2#post-15033 Tue, 06 Dec 2011 11:45:38 +0000 Hui 15033@http://chandoo.org/forums/ <p>Indian</p> <p>A cells value in A1:A19 can only be 1 value</p> <p>So =SUMPRODUCT(--(A1:A19=G1:G3)) returns the #N/A error as it should</p> <p>If you want to add the values that equal G1, G2 and G3<br /> =SUMPRODUCT(--(A1:A19=G1))+SUMPRODUCT(--(A1:A19=G2))+SUMPRODUCT(--(A1:A19=G3)) </p> http://chandoo.org/forums/topic/sumproduct-2#post-15032 Tue, 06 Dec 2011 11:19:23 +0000 Indian 15032@http://chandoo.org/forums/ <p>That’s how I use it, but now there are three criteria to be matched G1 to G3, (and more)..how would this be achived. </p> http://chandoo.org/forums/topic/sumproduct-2#post-15031 Tue, 06 Dec 2011 11:12:45 +0000 vijaySharma 15031@http://chandoo.org/forums/ <p>Not sure what is the data behind the formula, however if you point to one cell only as below, it will give you the count of times that value exists in A1:A19.</p> <p>=SUMPRODUCT(--(A1:A19=G1))</p> <p>G1:G3 will not work as the array elements are not matching</p> <p>~VijaySharma </p> http://chandoo.org/forums/topic/sumproduct-2#post-15029 Tue, 06 Dec 2011 11:00:58 +0000 Indian 15029@http://chandoo.org/forums/ <p>Hey Friends,</p> <p>Any idea as how would this work =SUMPRODUCT(--(A1:A19=G1:G3)) </p>