The idea of using two minus signs together in an Excel formula might seem odd to many. However, doing so offers some rather interesting and powerful options. In this tip, we will describe and demonstrate how and when you should use two minus signs in tandem when building Excel formulas.

As everyone knows, a single minus sign in a formula causes Excel to change the sign of the result – for example, from positive to negative. But what do two minus signs in a formula do? Using two minus signs in an Excel formula causes the formula to transform a result of “True” into a value of “1” and a result of “False” into a value of “0.” Using two minus signs in a formula has no effect on any result other than “True” or “False.”

In the very simple examples shown in Figure 1, cell C3 contains the formula shown in cell B3, =(5>3), and because that condition is true, the result of the formula is “TRUE,” as pictured in cell C3. However, the formula in cell C4 is that shown in cell B4, =--(5>3). Though the condition is still true, the result of the formula appearing in cell C4 is “1,” because of the presence of the double minus signs.

Figure 1 - Sample Formula With Double Minus Signs

Perhaps the most common application of using this technique is found when using Excel’s SUMPRODUCT function to sum data that meets multiple criteria. For instance, in the worksheet pictured in Figure 2, we could use a SUMPRODUCT formula with double minus signs to sum all of the data where the Region is “Midwest” and the Product Line is “Masks.”

Figure 2 - Sample Data For SUMPRODUCT

The syntax of that formula would be as shown below.