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.
Training articles
- Training Logical Test – Beginner Level
- Training Logical Test – Intermediate Level
- Excel Test for IF, IFS & IFERROR functions
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. The logical test should be placed in parentheses to ensure the result is always accurate.
=(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)
