-- in SUMPRODUCT function?

[fusella]

Hi everyone - first post

In the following function, what do the -- mean?

=SUMPRODUCT(--(E7:E52="Yes"),--(I7:I52=1))

Thanks in advance

[Ron Coderre]

That is called a "double unary". Here's why and how it's used:

Excel does not automatically consider boolean values (TRUE/FALSE) to be numbers. You need to coerce them into becoming numbers.

That is done by applying an arithmetic operator (+,-,*,/) to them.
Then Excel converts TRUE to 1, FALSE to 0.

For example:
If A1:=TRUE

B1: =ISNUMBER(A1)
returns FALSE because Excel doesn't recognize TRUE as a numeric value.

But all of these return 1:
B1: =ISNUMBER(A1+0)
B1: =ISNUMBER(A1/1)
B1: =ISNUMBER(A1*1)
B1: =ISNUMBER(--A1)

However, the first 3 of those formulas might be confused as an attempt to do a calculation.

Consequently, experienced Excel users understand that a Double-Unary (--)
indicates that we are forcing a conversion, not peforming a calculation.

It works this way:
-TRUE becomes -1
--TRUE becomes 1 (because the negative of a negative number is a positive number)

Does that help?

[fusella]

not really - I think its a little too technical for me.

Thanks though