How to Use (--) this in Excel Formula

Question

I found (--) this to add criteria in a sum Function, can you please tell me its exact use. And is this function(--) (whatever it is called by) can be used in any other formula?

Answer 1

It is not a standalone function, just two minus signs next to each other.

It is used to convert boolean (TRUE / FALSE) values to numbers, as those do not always evaluate correctly in formulas.

The first - converts TRUE to -1, and the second one changes it to 1.
The first - converts FALSE to 0, and the second one doesn't change it).

---

Can You also tell me the list of formula in which it can be used. Like one of them is SUMPRODUCT (I know only this one formula).

-- is just two operators next to each other, the "in which it can be used" question isn't really applicable to it. The right question would be to which data type it can be applied:

• If you apply it on a boolean (like --TRUE) it converts it to a number as explained above. Applying it on boolean has the same output as IF(<input>,1,0), but it's just shorter.
• If you apply it on a number (--5) it returns the original number.
• If you apply it on text (--"abc") it returns #VALUE error (like all other arithmetic operators).

SUMPRODUCT is not an example "where it can be used", but an example "where it's practical to use it". You can use it in any functions, but either you generally don't need it or people use IF instead.

One example for its use within SUM is to count even numbers in a range:
=SUM(--(MOD(E1:E4,2)=0))
(note that it's an array formula, so press CTRL+SHIFT+ENTER when you've entered it.)

Answer 2

1. Function (--) convert a return value of “TRUE” into 1 and a return value of “FALSE” into 0. It does not not effect the result.It is generally used with logical functions to convert Boolean type result to 0 or 1 form.
2. It is also used with SUMPRODUCT formula in excel as it neglects non -numeric values. Using double minus user is able to convert TRUE/FALSE value to 1/0 form