Do you know you can convert the result of a logical test, TRUE or FALSE, to 1 or 0? It's straightforward and highly useful for utilizing functions like SUM. Two techniques can be used
- Multiply the outcome of a logical test by 1
- Apply the hyphen method to transform the result (my favorite)👍
Result of a logical test in Excel
In Excel, a logical test returns TRUE or FALSE. For instance, to determine if a customer is 21 years old or older, we can write the following test with an absolute reference to the limit cell.
=B2>=$G$4
- In column D, you have the formula applied from column C.
Related articles
- Training Logical Test – Beginner Level
- Training Logical Test – Intermediate Level
- The IF Function with Excel
- Build a nested IF in Excel
How to convert the test to a numeric value?
Method 1: Multiply by 1
The first method is to multiply the logical test by 1. To ensure the result is always accurate, it is recommended that the logical test be placed in parentheses.
=(logical test)*1
Method 2: Add 2 hyphens before the logical test
The other technique is to write 2 hyphens before the test, as in this example, with the same result
=--(logical test)
Using this technique with a SUM function
Now, because we can convert true and false to 0 and 1, we can include logical test in a function like SUM. But this time, we must perform the test on the range of cells, and not only one cell like we did previously.
=SUM(--(B2:B9>=F4))
Of course, you can have a similar result with the COUNTIF function =COUNTIF(B2:B9,">="&F4)
