Cellwise Start free

Excel guide · 7 min read

How to convert TRUE and FALSE to 1 and 0 in Excel

Quick answerPut two minus signs in front of the cell: =--C2 returns 1 for TRUE and 0 for FALSE. =C2*1 and =IF(C2,1,0) give the same result. To add up a whole TRUE/FALSE or checkbox column, use =SUMPRODUCT(--C2:C9) or =COUNTIF(C2:C9,TRUE), because =SUM(C2:C9) skips logical values and returns 0.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFG
1TaskOwnerDoneEst. hoursActual hoursApproved (import)Remote (1/0)
2Write job adPriyaTRUE32.5TRUE1
3Screen CVsPriyaTRUE68TRUE0
4Book interviewsTomTRUE22FALSE0
5First interviewsTomFALSE89.5TRUE1
6Reference checksPriyaTRUE34FALSE1
7Send offerLenaFALSE10FALSE0
8Order laptopLenaTRUE11.5TRUE0
9Plan first weekTomFALSE40FALSE1

Download this sheet as an Excel practice file (.xlsx)

Four ways to turn TRUE into 1

The sample is a hiring checklist with eight tasks. Column C holds checkboxes (Insert > Checkbox in Microsoft 365), so each cell stores TRUE when ticked and FALSE when not. Excel shows the box, but formulas see the logical value. Any arithmetic converts it: TRUE becomes 1 and FALSE becomes 0.

The double minus, =--C2, is the shortest. The first minus turns TRUE into -1 and the second turns it back into 1. =C2*1 and =C2+0 do the same with a multiplication or addition that changes nothing. =IF(C2,1,0) is the longest but the easiest to read for someone who has never seen --, and you can swap the 1 and 0 for any other pair of values. There is also =N(C2), which returns 1 for TRUE, but N quietly returns 0 for any text, so a typo never shows up as an error.

Which to pick: use -- inside formulas such as SUMPRODUCT, where it's the common convention and other Excel users will recognise it. Use IF when the result should be something other than 1 and 0, such as "Yes" and "No": =IF(C2,"Yes","No"). In Excel 365 one formula converts the whole column and spills down, as below.

=C2:C9*1

Result: spills 8 rows × 1 column

1
1
1
0
1
0
1
0

Spills 1, 1, 1, 0, 1, 0, 1, 0: one number per task, 1 where the box is ticked. =--C2:C9 gives the same column.

Why SUM of TRUE/FALSE cells returns 0

This is the reason most people go looking for the conversion. Five boxes are ticked, yet =SUM(C2:C9) returns 0. SUM, AVERAGE, MAX and the other functions that take a range ignore logical values in that range, the same way they ignore text. No error, no warning, just 0.

The odd part is that =C2+C3 returns 2. The + operator converts its two values before adding, so TRUE+TRUE is 2. SUM even counts logical values when you type them straight into its arguments, so =SUM(TRUE,TRUE) is 2 as well. It is only the range that gets skipped.

SUMPRODUCT behaves like SUM here: =SUMPRODUCT(C2:C9) is also 0, because it treats anything that is not a number as 0. That's why you see the double minus in so many SUMPRODUCT formulas: it turns the TRUE/FALSE array into 1s and 0s first. The same trap shows up with numbers stored as text, which Excel formula returns 0 walks through cause by cause.

=SUM(C2:C9)

Result: 0

0, even though five boxes are ticked. SUM skips the TRUE values in the range.

Totals: count ticks and sum the hours of finished tasks

To count the TRUE cells you don't need to convert anything: =COUNTIF(C2:C9,TRUE) returns 5. Write TRUE without quotes, because you're matching the logical value. =COUNTIF(C2:C9,FALSE) returns the 3 open tasks.

To add up something for the ticked rows, use the checkbox column as a criterion: =SUMIFS(D2:D9,C2:C9,TRUE) returns 15, the estimated hours of the finished tasks. The arithmetic version, =SUMPRODUCT(--C2:C9,D2:D9), returns the same 15 and works when the condition is more complicated than one column. Without the --, =SUMPRODUCT(C2:C9,D2:D9) returns 0.

In Excel 365 and 2021, =SUM(--C2:C9) also returns 5, because SUM can work on the converted array. In Excel 2019 and earlier the same formula has to be entered with Ctrl+Shift+Enter, so SUMPRODUCT is the safer choice in a file that people open in different versions.

=SUMPRODUCT(--C2:C9)

Result: 5

5: the double minus turns the five TRUE values into 1s. COUNTIF(C2:C9,TRUE) gives the same count.

A checkbox column as a completion rate

Because 1 means done and 0 means not done, the average of the converted column is the share of tasks finished. =AVERAGE(C2:C9) does not work: AVERAGE skips every logical value in the range, finds nothing to average and returns #DIV/0!. =AVERAGE(--C2:C9) returns 0.625 in Excel 365. In every version, divide the count by the number of rows instead: =SUMPRODUCT(--C2:C9)/ROWS(C2:C9). AVERAGEA is a third option: it counts TRUE as 1 and FALSE as 0 inside a range, so =AVERAGEA(C2:C9) gives the same 62.5%.

Watch the denominator if your boxes are old form-control checkboxes linked to cells. A linked cell stays empty until someone clicks the box for the first time, so COUNTIF(range,FALSE) misses those tasks. ROWS counts every row, and -- turns an empty cell into 0, so this formula treats a never-clicked box as not done, which is usually what you want.

Five of eight tasks is 62.5%, but they aren't equal tasks. Weighting by hours, =SUMPRODUCT(--C2:C9,D2:D9)/SUM(D2:D9) returns 53.6%: 15 of the 28 estimated hours are done, because the big job (First interviews, 8 hours) is still open. For one owner, =COUNTIFS(C2:C9,TRUE,B2:B9,"Tom")/COUNTIF(B2:B9,"Tom") gives 1 of 3, or 33.3%. Percentage of tasks completed covers status columns, progress bars and weighting in more depth.

=SUMPRODUCT(--C2:C9)/ROWS(C2:C9)

Result: 0.625 shows as 62.5% with Percent format

0.625, which shows as 62.5% done. Weighted by estimated hours it's 53.6%.

Comparison results are TRUE/FALSE too

Any comparison returns a logical value. =E3>D3 asks "did Screen CVs take longer than estimated?" and returns TRUE (8 hours against 6). Wrap it in -- and you get a 1 you can add: =SUMPRODUCT(--(E2:E9>D2:D9)) counts the 4 tasks that ran over. Keep the brackets around the comparison: without them, =SUMPRODUCT(--E2:E9>D2:D9) applies the minus signs to the hours, not to the result, and returns 0 because the TRUE/FALSE values are never converted.

To combine conditions, multiply them: =SUMPRODUCT((E2:E9>D2:D9)*C2:C9) returns 3, the finished tasks that ran over. Multiplying already converts TRUE and FALSE, so you need -- only when a condition stands alone. Multiplying means AND; adding the conditions and testing for more than 0 means OR. For tasks still open, test the box for FALSE: (C2:C9=FALSE), or wrap it in NOT.

=SUMPRODUCT(--(E2:E9>D2:D9))

Result: 4

4: Screen CVs, First interviews, Reference checks and Order laptop took more hours than estimated.

When TRUE is text: -- returns #VALUE!

Column F came from another system, and its TRUE and FALSE are text, not logical values. They look the same, but text sits on the left of the cell while real logical values are centred by default. =ISTEXT(F2) returns TRUE, and =ISLOGICAL(F2) returns FALSE. Text TRUE turns up after pasting from a web page or another app, from a Power Query column typed as text, and from formulas that put quotes around the result, such as =IF(E2>D2,"TRUE","FALSE").

The double minus can't convert the word TRUE, so =--F2 returns #VALUE!, and VALUE(F2) fails the same way. N(F2) is worse: it returns 0 for any text, so every row looks like FALSE without an error. A comparison against a real TRUE fails too: =C2=F2 returns FALSE even though both cells show TRUE, because a logical value never equals a text value.

Compare the text instead: =--(F2="TRUE") returns 1, and the comparison ignores case, so "true" works too. For the whole column, =SUMPRODUCT(--(F2:F9="TRUE")) counts the 4 approved tasks, and =SUMPRODUCT(C2:C9*(F2:F9="TRUE")) finds the 3 that are both done and approved. To fix the column for good, put =F2="TRUE" in a helper column, then copy it and use Paste Special > Values over the original.

=--F2

Result: #VALUE!

#VALUE!: F2 holds the text "TRUE", which -- can't turn into a number. =--(F2="TRUE") returns 1.

The reverse: 1 and 0 back to TRUE and FALSE

Column G is a Remote flag exported as 1 and 0. A comparison turns it back into logical values: =G2=1 returns TRUE for 1 and FALSE for anything else. If the column might hold other non-zero numbers that should also count as TRUE, use =G2<>0 instead.

To get checkboxes, convert first: fill the comparison down a helper column, copy it, Paste Special > Values over G2:G9, then select the cells and choose Insert > Checkbox. A checkbox shows ticked or unticked for TRUE and FALSE, so the values need to be logical before you add the boxes. If you only want the 1s and 0s to read better without changing them, a custom number format such as "Yes";"Yes";"No" shows any positive number as Yes and 0 as No while the cell still holds a number you can add.

=G2:G9=1

Result: spills 8 rows × 1 column

TRUE
FALSE
FALSE
TRUE
TRUE
FALSE
FALSE
TRUE

Spills TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, TRUE: the 1s become TRUE and the 0s become FALSE.

Common mistakes

Adding a checkbox column with SUM and getting 0

SUM skips TRUE and FALSE in a range. Use =COUNTIF(C2:C9,TRUE) to count the ticks, or =SUMPRODUCT(--C2:C9) to add them as 1s.

SUMPRODUCT with a TRUE/FALSE range but no --

SUMPRODUCT treats logical values as 0, so =SUMPRODUCT(C2:C9,D2:D9) returns 0. Write --C2:C9, or multiply the ranges inside one argument: =SUMPRODUCT(C2:C9*D2:D9).

#VALUE! from --, or every row counted as 0

The TRUE and FALSE are text. Check with =ISTEXT(F2), then compare instead of converting: =--(F2="TRUE").

Averaging the checkbox cells directly

=AVERAGE(C2:C9) skips the logical values and returns #DIV/0!. Use =SUMPRODUCT(--C2:C9)/ROWS(C2:C9), or =AVERAGE(--C2:C9) in Excel 365.

FAQ

How do I convert TRUE to 1 in Excel?

Use =--A2, which returns 1 for TRUE and 0 for FALSE. =A2*1 and =IF(A2,1,0) give the same result, and in Excel 365 =--A2:A20 converts a whole column at once.

What does -- (double minus) mean in an Excel formula?

It's two negations in a row. Each one forces Excel to treat the value as a number, so TRUE becomes -1 and then 1, and FALSE becomes 0. It's used mostly inside SUMPRODUCT, which treats TRUE and FALSE as 0 otherwise.

Why does SUM return 0 for TRUE values?

SUM ignores logical values (and text) inside a range, so a column of TRUE and FALSE adds up to 0. Count them with =COUNTIF(range,TRUE), or convert them with =SUMPRODUCT(--range).

How do I convert TRUE/FALSE to Yes/No in Excel?

Use =IF(A2,"Yes","No"). The IF tests the logical value directly, so you don't need =A2=TRUE. If the cell holds the text "TRUE", use =IF(A2="TRUE","Yes","No") instead.

How do I count checkboxes that are not ticked?

Use =COUNTIF(range,FALSE). For old form-control checkboxes linked to cells, add the empty cells too, because a linked cell stays blank until the box is first clicked: =ROWS(range)-COUNTIF(range,TRUE) counts every unticked box.

Functions used

Related guides

Updated