"--" used in sumproduct function

**jajone4** · 04-28-2014, 05:58 PM

Can someone explain to me what "--". I cannot find a description for this

INDEX(--(B1:B20>=100)

Thank you in advance

**samdthompson** · 04-28-2014, 06:06 PM

Could be something missing here. As it stands this formula would not return a result. There are missing brackets and the syntax is incorrect for an INDEX formula.

**daddylonglegs** · 04-28-2014, 06:18 PM

Agreed, there seems to be something missing.....but this part

B1:B20>=100

will return an "array" of TRUE or FALSE values, one each for all values in B1:B20.

When you add -- like this

--(B1:B20>=100)

then that "co-erces" TRUE to 1 and FALSE to zero so you get an array of 20 1/0 values, you could do the same by adding zero like this

(B1:B20>=100)+0

or multiplying by 1 like this

(B1:B20>=100)*1

although if you just want to count the number of values in B1:B20 that are >=100 you don't need any of those, you can use COUNTIF like

=COUNTIF(B1:B20,">=100")

**Tony Valko** · 04-28-2014, 06:23 PM

Check out these links:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

**jajone4** · 04-28-2014, 07:17 PM

So sorry, meant to post a different function but i was leaving work and rushing a little...I saw it in another thread when i was working through a sum product issue and "--" was used.

=SUMPRODUCT(--(C4:C8="RENEW"),--(D4:D8="John"),(J4:J8))

I never really used sumproduct so when I saw "--" , I wasnt sure what its purpose was

**Tony Valko** · 04-28-2014, 10:25 PM

SUMPRODUCT is an extremely versatile function.

Read through the xldynamic link in my other reply for some examples of how it can be used.

**sktneer** · 04-28-2014, 10:45 PM

To know, how the double negative works in Sumproduct, see the attached sheet where a simple Sumproduct formula is used, showing all the steps involved.