Skip to content

The Double Negative Operator in Excel

Take a look at this formula.

=SUM(--(A2:A6=B2:B6))

Have you ever seen the double negative operator before? Do you know what it does?

In this article, I will show you.

The Double Negative Operator

The double negative operator transforms TRUE/FALSE values to numerical 1s and 0s.

Take the following example. The formula in cell D1 results in an array of TRUE/FALSE values.

Watch what happens when you apply the double negative operator to this formula.

=--(A2:A6=B2:B6)

The TRUE/FALSE values transform into 1s and 0s.

Why Use the Double Negative?

Typically, you want to use the double negative operator whenever you want to perform numerical operations on your array of TRUE/FALSE values.

Transforming the TRUE/FALSE values into 1s and 0s makes numerical calculations possible.

For example, let’s say you want to count the number of matches between the two datasets. You can write the following formula.

=SUM(--(A2:A6=B2:B6))

The double negative transforms the TRUE/FALSE array into 1s and 0s, and the SUM function adds together all of the numbers.

Although you may not use the double negative operator often, it’s nice to know that it exists and how to use it.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top