Excel formula query in sumproduct

**plabh** · 05-03-2013, 06:15 AM

After a long search in internet, I came across a formula which solved my task. But I am not able to understand meaning & requirement of "--" in the formula. The formula is: =SUMPRODUCT(--ISNUMBER(FIND($A2,$F$6:$F$23)),$B$6:$B$23)
If I dont put "--" in the formula before isnumber, it gives me wrong result and putting "--" yield the right one.
Can someone please explain the use & requirement of it?

Tons of thanks in advance

P.S.: I needed this formula to sum a set of value based on a critieria. I had two criteria i.e. page and Page. It was a case sensitive operation. As sumif alone cannot sum values with case-sensitive criteria, so I had to go with other options.

**mvsub1** · 05-03-2013, 06:28 AM

Normally, sumproduct evaluates an array into a series of trues and falses. Sometimes you need these to be ones and zeroes to perform calculations with them. The double negative converts a string of {TRUE,FALSE,FALSE,FALSE,TRUE} to {1, 0, 0, 0, 1}.

Check these two links out for more info on sumproduct:
'Double negative' explained
More on sumproduct

**dilipandey** · 05-03-2013, 06:50 AM

Hi Plabh,

"--" is used to covert the matched "TRUEs" to 1 and "FALSEs" to 0.. so where ever A2 is present in F6: F23, you get all 1s and then these will be multiplied the respective values in the range B6:B23 - ultimately giving you the sum from column B

Regards,
DILIPandey
<click on below * if this helps>

**plabh** · 05-03-2013, 07:12 AM

Originally Posted by mvsub1

Thanks for your kind help & Links, mvsub1!!!

Originally Posted by dilipandey

Thanks for your insights here, dilipandey!!!

I understood that it converts all True to 1. Changes into Boolean. But can you guys please tell me that what is the use of it and how we can apply in future problems.

Again appreciate for your prompt & kind help!!!

**dilipandey** · 05-03-2013, 07:43 AM

HI Plabh,

I would suggest you to search internet for SumProduct function and its usages .. you can also view uTube videos on this function.
This will give you fare idea about it and its future usages. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**plabh** · 05-03-2013, 08:18 AM

Originally Posted by dilipandey

HI Plabh,

I would suggest you to search internet for SumProduct function and its usages .. you can also view uTube videos on this function.
This will give you fare idea about it and its future usages. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

DILIPandey, thanks for your guidance. Well, I am aware of the usage of sumproduct. But I was concerned and worried about usage of "--" doublenegataion before isnumber in my formula. Anyways, thank you very much!!!

**dilipandey** · 05-03-2013, 11:08 AM

I already explained the usage of -- in my post#3

and why I referred Sumproduct function is because "-- used frequently with this function, may be you can get some video tutorials on this.

Regards,
DILIPandey
<click on below * if this helps>

**bebo021999** · 05-03-2013, 12:03 PM

OK. Let see you have data like this:
----B----------F
1--10-----" Try Page 1"
2--20-----" Refer to page 1"
3--30-----" I like Page 1"
A2= "Page"
now you want to sum B1:B3 with criteria in A2 that F1:F3 contain "Page" (not "page"). The result should be 10+30=40
First, check the criteria: type this in a cell, i.e, A3=FIND(A2,F1:F3), hit F9, to see the array: {5;#VALUE;8}, indicate that F2 does not contain "Page"
Now, we need to convert to {1;0;1} in order to multiply by B1:B3{10;20;30}
use: ISNUMBER(FIND(A2,F1:F3)) = {TRUE;FALSE;TRUE}
convert to number:
--ISNUMBER(FIND(A2,F1:F3)) or 1*ISNUMBER(FIND(A2,F1:F3)) or ISNUMBER(FIND(A2,F1:F3))+0
={1;0;1}
Now use SUMPRODUCT:
SUMPRODUCT(--ISNUMBER(FIND(A2,F1:F3)),B1:B3)=SUMPRODUCT({1;0;1},{10;20;30})=1*10+0*20+1*30=40
That is how your formula works.
But, in this case, donot need to convert boolean to number:
=SUMPRODUCT((ISNUMBER(FIND(A2,F1:F3)))*B1:B3) = SUMPRODUCT(TRUE;FALSE;TRUE}*{10;20;30} is enough, because boolean*number (or +,-,...) is converted automatically to number
Hope it is clear for you.