Turn TRUE/FALSE into 1/0

Excel Formulas › Logical

All versionsCoercion

To count or sum logical results, convert TRUE/FALSE to 1/0. The double-negative (--) is the pro’s way; multiplying or adding zero works too.

Quick formula: count how many values pass a test:
=SUMPRODUCT(--(A2:A100>100))
-- coerces each TRUE/FALSE to 1/0; SUMPRODUCT sums them — the count of values over 100.

Functions used (tap for the full reference guide):

SUMPRODUCT

The example

Counting the TRUEs across a range.

AB
1TestAs number
2TRUE1
3FALSE0

The formula

Coerce, then sum:

=SUMPRODUCT(--(A2:A100>100)) // counts the TRUEs

How it works

A math operation flips boolean to numeric:

  1. A logical test gives TRUE/FALSE; arithmetic forces it to 1/0.
  2. The double unary -- is the cleanest: --(A2>100) is 1 or 0.
  3. (A2>100)*1 and (A2>100)+0 do the same.
  4. Inside SUMPRODUCT or SUM, this counts or weights rows meeting a condition — the engine behind many array formulas.

Multiply conditions for AND, add for OR. --((a)*(b)) counts rows meeting both; this 1/0 coercion is what makes SUMPRODUCT a flexible conditional counter when COUNTIFS can’t express the logic.

Try it: interactive demo

Live demo

Values; count those over the threshold.

Count:

Variations

Multiply version

Same as --:

=SUMPRODUCT((A2:A100>100)*1)

Weighted sum

Condition times values:

=SUMPRODUCT((cond)*amounts)

Single cell

1 or 0:

=--(A2>100)

Pitfalls & errors

Booleans don’t auto-sum. Some functions ignore TRUE/FALSE — coerce to 1/0 with -- first.

Two minus signs. A single - negates (TRUE→−1); use two to get a positive 1.

Text won’t coerce cleanly. The trick works on logical results, not arbitrary text.

Practice workbook

📊
Download the free Turn TRUE/FALSE into 1/0 practice workbook
A coercion sheet with the multiply, weighted, and single-cell variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I convert TRUE/FALSE to 1/0 in Excel?
Use a double unary: =--(A2>100) gives 1 or 0. Multiplying by 1 or adding 0 works too.
How do I count values meeting a condition with SUMPRODUCT?
Coerce and sum: =SUMPRODUCT(--(range>100)) counts the TRUEs.
Why two minus signs?
One minus negates a boolean to -1; two minuses flip it back to a positive 1. Use --.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: SUMPRODUCT formula · Count rows OR conditions · Yes/No from a test

Function references: SUMPRODUCT