What the Double Negative (--) Does in Excel Formulas

The double negative in Excel, written --, turns TRUE into 1 and FALSE into 0. You'll see it in formulas like =SUMPRODUCT(--(B2:B7="East")), where it makes a list of yes-or-no answers into numbers that can be counted and added. This guide explains what each minus sign does, when you need -- and when you don't, with examples you can type in.

What does the double negative (--) do?#

The double negative is two minus signs in a row. A single minus in front of a value makes it negative. Excel treats TRUE as 1 when it does math, so one minus turns TRUE into -1, and a second minus turns it back to 1:

So -- doesn't change a number; it changes the type. TRUE and FALSE become 1 and 0, and text that looks like a number becomes a real number. The fancy name for it is "double unary", because a minus in front of a single value is called a unary operator.

Why SUMPRODUCT needs the double negative#

A comparison like B2:B7="East" returns one TRUE or FALSE for each row. You can see this in the TRUE, FALSE and comparisons lesson. The problem is that SUMPRODUCT treats TRUE and FALSE as zero, so it can't count them. All the examples below use this sales table in A1:D7:

Compare these two formulas:

=SUMPRODUCT((B2:B7="East"))
=SUMPRODUCT(--(B2:B7="East"))

The first returns 0, even though three rows are East. The second returns 3, because -- turns the six answers into {1;0;1;0;1;0} before SUMPRODUCT adds them. The SUMPRODUCT function guide and the full SUMPRODUCT article cover the function itself.

How to sum with a condition using --#

Give SUMPRODUCT the 1s and 0s as one array and the numbers as another. It multiplies them row by row and adds the results:

=SUMPRODUCT(--(B2:B7="East"),D2:D7)

Row by row that is 1×820 + 0×450 + 1×610 + 0×990 + 1×230 + 0×540, which gives 1660. Each 0 removes a row that failed the test, and each 1 keeps it.

When you don't need the double negative#

You only need -- when TRUE and FALSE would otherwise reach SUMPRODUCT on their own. Any math operation converts them too, so multiplying two tests together already gives 1s and 0s:

=SUMPRODUCT((B2:B7="East")*(D2:D7>500))

This counts East rows with sales over 500 and returns 2 (Ana and Chen). Adding -- in front of each test would also work but isn't needed. The same goes for *1 or +0: all three ways convert TRUE to 1. Pick one and use it consistently.

For plain counts and sums with simple conditions, COUNTIFS and SUMIFS are easier to read and need no conversion at all:

=SUMIFS(D2:D7,B2:B7,"East")

This returns the same 1660. The COUNTIFS and SUMIFS lesson teaches them, and SUMPRODUCT vs SUMIFS and COUNTIFS vs SUMPRODUCT explain when the longer SUMPRODUCT version is worth it, such as testing part of a cell or doing math on a column before comparing.

Using -- with ISNUMBER and SEARCH#

The double negative shows up a lot with ISNUMBER and SEARCH, to count cells that contain a word anywhere in the text:

=SUMPRODUCT(--ISNUMBER(SEARCH("laptop",C2:C7)))

SEARCH finds "laptop" in each product name and returns a position number, or #VALUE! when it isn't there. ISNUMBER turns those into TRUE or FALSE, and -- turns them into 1 or 0. The result is 4: Laptop Pro twice and Laptop Air twice. No brackets are needed around ISNUMBER, because a function result is already one value. The guide to ISNUMBER with SEARCH covers this pattern in depth.

Using -- to turn text into numbers#

The double negative also converts numbers stored as text. If A2 holds the text "0042":

=--A2

returns the number 42. It's handy inside other formulas, such as =--LEFT(A2,4) to turn the first four digits of a code into a number you can compare with > or <. VALUE does the same thing and is easier to read: =VALUE(A2). If the text isn't a number at all, both return #VALUE!.

Common mistakes with the double negative#

Most -- problems come from brackets and from the order Excel works in:

1. Missing brackets around the test. Excel applies the minus signs before comparisons. So --B2:B7="East" tries to make the text in B2:B7 negative first and returns #VALUE!. Always write --(B2:B7="East"). The comparison operators guide lists the order Excel uses.
2. Putting -- outside the whole formula. =--SUMPRODUCT((B2:B7="East")) still returns 0, because SUMPRODUCT has already ignored the TRUE values. The -- must touch the TRUE/FALSE array.
3. Using SUM in older Excel. =SUM(--(B2:B7="East")) works in Excel 2021 and Microsoft 365. In Excel 2019 and earlier, it needs Ctrl+Shift+Enter, so use SUMPRODUCT instead.
4. Ranges of different sizes. Every array in SUMPRODUCT must have the same number of rows, or it returns #VALUE!.

Practise it#

The double negative makes sense once you've seen comparisons return TRUE and FALSE yourself. Practise TRUE, FALSE and comparisons in a live spreadsheet, then practise COUNTIFS and SUMIFS so you know when -- isn't needed at all. Both check your formula as you type. Start with the exercise on this page: total the East sales in one formula.