Sumproduct

[vasim]

Hey Friends,


Any idea as how would this work =SUMPRODUCT(--(A1:A19=G1:G3))

 

[vijaySharma]

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.


=SUMPRODUCT(--(A1:A19=G1))


G1:G3 will not work as the array elements are not matching


~VijaySharma

 

[vasim]

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.

 

[Hui]

Indian


A cells value in A1:A19 can only be 1 value


So =SUMPRODUCT(--(A1:A19=G1:G3)) returns the #N/A error as it should


If you want to add the values that equal G1, G2 and G3

=SUMPRODUCT(--(A1:A19=G1))+SUMPRODUCT(--(A1:A19=G2))+SUMPRODUCT(--(A1:A19=G3))

 

[vasim]

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.


What does -- mean in the formula.........

 

[NARAYANK991]

Hi ,


Any of the following should tell you all that you want to know :


1. http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/


2. http://www.exceluser.com/explore/sumproduct_11.htm


3. http://www.excel-easy.com/excel-examples/sumproduct.html


4. http://www.excel-examples.com/sumproduct.htm


5. http://www.tutorialized.com/view/tutorial/SUMPRODUCT/63027


6. http://www.computergaga.com/excel/functions/sumproduct.html


Narayan

 

[keymaster]

the -- refers to double negation. The portion (A1:A10=B1) returns only TRUE & 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 & 0s, which the SUMPRODUCT then sums up.


Read up this for more: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

 

[vasim]

Thanks.......amazing stuff....You are like a drug..slowly slowly getting in our blood and making us addicted for excel knowledge.