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 number, as those not always evaluate correctly in formulas.

First - converts TRUE to -1, and second one changes it to 1.
First - converts FALSE to 0(, and 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, "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 convert it to a number as explain above. Applying it on boolean it has a 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 a 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", you can use it in any functions, but either you generally don't need it, either people use IF instead.

One example for its use within SUM 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