I used to see the double dash (–) in Excel spreadsheets and wonder what it did. It’s an essential shortcut to force Excel to treat TRUE and FALSE as the numbers 1 and 0, meaning you can easily sum or count the results of a logical test across a range. Now I know, and I want to share this powerful trick with you.
The core function: Boolean-to-numeric coercion
The double-unary forces a value from one data type (Boolean) to another (numeric)—a process called type coercion. Let me show you a straightforward example.
This formula returns TRUE because the value in cell A1 is greater than 10:
=A1>10
A single minus sign in the formula converts TRUE to its negative numeric equivalent (-1):
=-(A1>10)
Adding a second minus (thus, the double-unary sign) reverses the number to 1.
=--(A1>10)
If the value in cell A1 doesn’t meet the Boolean criteria, the double-unary returns 0.
The Beginner’s Guide to Boolean Logic in Microsoft Excel
Boost your Boolean boon.
The problem: Why logical arrays fail simple sums
When you create an array of logical results, the SUM function treats TRUE and FALSE as non-numeric and ignores them, resulting in 0.
In this worksheet, suppose you’re monitoring weekly sales totals and whether they meet the $500 goal. The formula in cell C2 spills an array returning TRUE and FALSE according to whether the corresponding values in column B meet the criteria:
=B2:B6>=F2
Now, rather than tell you whether you’ve met the goal for each week individually, you want Excel to count how many sales totals have achieved the goal overall. However, if you wrap the above formula inside SUM, it returns zero because the Boolean values are non-numeric:
=SUM(B2:B6>=F2)
In this specific case, you could use COUNTIF. However, I’m using the SUM function only to demonstrate how the double-unary operator forces the numeric conversion of the underlying array.
The double-unary converts the array elements, meaning the SUM function can process them:
=SUM(--(B2:B6>=F2))
Here, behind the scenes, Excel turns the array from {TRUE, FALSE, TRUE, FALSE, FALSE} to {1, 0, 1, 0, 0}, which is why we get the correct count of 2.
The power use case: Counting when COUNTIFS fails
The double-unary operator truly shines when you need to perform complex array logic that built-in functions like COUNTIFS can’t execute. The primary limitation of COUNTIFS is that its criteria are static—it can’t dynamically compare to ranges on a row-by-row basis.
Imagine you need to count how many salespersons exceeded their individual goals in this spreadsheet.
To do this correctly, you need to compare each cell in the Sales column to its neighbor in the Goal column. If you attempt to use COUNTIFS to do this, it produces an unexpected result:
=COUNTIFS(B2:B6,">"&C2:C6)
This is because, instead of comparing B2 to C2, B3 to C3, and so on, Excel treats the entire range (C2:C6) as an array of multiple criteria to be applied to the same range (B2:B6). This returns an array of incorrect results ({3, 1, 3, 2, 3}) because it counts how many sales are greater than each individual goal value across the whole list. In other words, three sales totals are greater than 81, one is greater than 97, three are greater than 71, and so on.
Instead, you use SUMPRODUCT with the double-unary operator. This forces Excel to evaluate the logical test row-by-row:
=SUMPRODUCT(--(B2:B6>C2:C6))
Here’s how it works:
- Logical test: The comparison (B2:B6>C2:C6) returns the correct array of Boolean values: {FALSE, FALSE, TRUE, FALSE, TRUE}.
- Type coercion: The double-unary operator (–) converts this Boolean array into a numeric array: {0, 0, 1, 0, 1}.
- Final sum: The SUMPRODUCT function adds the elements: 0+0+1+0+1 to return 2, which is the correct count.
Like COUNTIFS, SUMIFS is also limited to checking static criteria. So, for example, if you want to sum sales only when they exceed the average sale, you also need to use the SUMPRODUCT function with the double-unary operator.
Why use SUMPRODUCT, not SUM?
In this example, you could use SUM instead of SUMPRODUCT. However, the difference between the two concerns compatibility. SUMPRODUCT handles arrays natively, automatically taking the numeric array generated by the double-unary operator and summing the elements. In Excel for Microsoft 365 and perpetual versions released in 2021 or later, SUM can do this automatically, although in older versions, you need to press Ctrl+Shift+Enter to commit the formula, which wraps it in curly braces:
{=SUM(--(B2:B6>C2:C6))}
In older versions of Excel, if you commit this formula with a simple Enter, it will return either an error or an incorrect value. So, SUMPRODUCT is simpler, safer, and backwards-compatible.
6 Functions That Changed How You Use Microsoft Excel
Dynamic array functions were a game-changer.
How about alternative methods?
Because Excel converts TRUE and FALSE to 1 or 0 if it sees a mathematical symbol directly before them, it’s fair to ask whether the double-unary operator is truly necessary. Since any arithmetic operation performs this type of coercion, there are indeed alternative formulas that achieve the same result:
You can multiply the entire logical array by 1:
=SUMPRODUCT((B2:B6>=F2)*1)
You can also add 0 to the array:
=SUMPRODUCT((B2:B6>=F2)+0)
These methods successfully convert Boolean values to numbers, meaning the array becomes countable. However, I still prefer using the double-unary operator for these reasons:
- It clearly signals the intent: The double-unary operator is the universally accepted Boolean-to-numeric coercion method. Its sole purpose is data type conversion, and seeing it immediately tells everyone who sees the spreadsheet exactly what the formula is doing. Using *1 or +0 is less explicit and might be mistaken for a separate arithmetic adjustment to the result.
- It’s cleaner and less cluttered: The double-unary operator is the most concise way to apply a mathematical coercion operation to an array. For example, if you have a complex nested array formula, two simple dashes help keep the overall logic simple.
- It’s marginally more efficient: While the difference is negligible, the double-unary operator can be more efficient in extremely large workbooks. When you use *1 or +0, Excel must process this as a separate operation on each cell; however, the double-unary operator simply converts the data type of the array values.
So, while the alternatives are perfectly functional, the double-unary operator is the cleanest, most efficient, and most professional way to perform the Boolean-to-numeric coercion.
The double dash isn’t the only Excel symbol that can unlock advanced spreadsheet and formula control. For example, the # symbol is vital in modern versions of the program for including all the results from a dynamic array in a formula, and the @ sign returns a single value from a range or array.
- OS
-
Windows, macOS, iPhone, iPad, Android
- Free trial
-
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
