Hadleigh Homes ticked nine plots off a snagging register. The application for payment claimed six of them. The three it missed were ticked on screen, in a column where every tick looks the same.
Larkfield Rise is a twelve-plot site. Before a plot can be handed over its snagging list has to be remediated and signed off, and the register is one sheet: plot, trade, what the remediation cost, and a checkbox the site manager ticks.
On 29 September the surveyor built the month's application off that sheet with one formula.
=SUMIFS(C2:C13,D2:D13,TRUE) → 6,940.75
Nine boxes were ticked. The total it returned covers six of them.
Three of those ticks are not ticks. Plots 18, 22 and 29 hold the word TRUE, pasted in from the site app's export, and a word is text. Text that reads TRUE is not the logical value TRUE, and nothing on the sheet treats the two alike.
| What the sheet was asked | Formula | Answer |
|---|---|---|
| Add the tick column up | =SUM(D2:D13) | 0 |
| Count the entries | =COUNTA(D2:D13) | 12 |
| Count the ticks | =COUNTIF(D2:D13,TRUE) | 6 |
| Count the empty boxes | =COUNTIF(D2:D13,FALSE) | 3 |
| Spend the ticks | =SUMIFS(C2:C13,D2:D13,TRUE) | £6,940.75 |
£16,736.50 of remediation had been signed off that month. £6,940.75 of it reached the application and £9,795.75 of it did not.
The certificate was signed on 30 September. The gap was found on 21 October, three weeks into the next payment cycle, by which time the only thing to do with £9,795.75 of finished and uncontested work was to claim it again next month.
Nobody checked the tick column, because there was nothing on screen to check. Nine ticks in one column, three of them left-aligned to anybody who knew that alignment meant something.
01Nine Ticks, Six That Count
Twelve Plots, Nine Ticks on Screen, and Six a Formula Can Count
The register holds one row per plot on a twelve-plot site: the plot, the trade that carried out the snagging remediation, what that remediation cost, and a sign-off column the site manager ticks before the plot can be claimed. Column D is what everybody reads and column E is what the cells actually hold, which is the half nobody sees: nine of the twelve rows read TRUE, but only six of them hold the logical value TRUE. The other three — Plots 18, 22 and 29 — hold the word, pasted in from the site app's export, and they are worth £9,795.75 between them. Every cost is in pounds. The twelve plots total £31,972.00, the nine signed-off plots total £16,736.50, and the six that a criteria of TRUE can match total £6,940.75, which is the figure that went into the application for payment.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Column D is the register as everybody reads it. Column E is what the cells hold, which is the half nobody sees. The rows that differ in column E are identical in column D, and column D is the entire user interface.
The three text rows are not scattered randomly either. They are the three plots the site app exported, so the fault arrived in one paste, on one afternoon, and it will arrive again on the next one.
Scenario: Open a sheet of your own with a tick, a flag or a Yes/No column in it. Put =COUNTA(D2:D13)-COUNTIF(D2:D13,TRUE)-COUNTIF(D2:D13,FALSE) underneath that column. Anything other than 0 is the number of entries that are not logical values.
02A Checkbox Is a Value, Not an Object
Insert ▸ Checkbox puts the box inside the cell. The cell's value is the logical TRUE or FALSE, and the box is a format drawn over that value — the same relationship a currency format has with a number.
Clicking it toggles the value. So does the space bar on a selected range, which clears or ticks a whole column in one keystroke. Everything else about the cell is ordinary: it sorts with its row, filters with its row, and any formula can read it.
The older control is a different animal. Developer ▸ Insert ▸ Check Box draws a Form Control that floats above the grid with a Cell Link pointing at some cell. Sort the rows and the values move while the boxes stay exactly where they were drawn.
That is the one reason to prefer the in-cell box on any list that will ever be sorted or filtered: the tick and the row cannot come apart.
What this covers.
SUM,COUNT,COUNTA,COUNTIF,COUNTIFS,SUMIFS,SUMPRODUCT,ISTEXT,IF,AND,TRIMandUPPERwork in every version this century.In-cell checkboxes are new: Insert ▸ Checkbox arrived in Excel for Microsoft 365 and Excel for the web in 2024, and is not in Excel 2021 or earlier.
FILTER,XLOOKUPandLETneed Microsoft 365 or Excel 2021.Everything else here is about logical values, which have behaved this way for forty years. A column somebody typed TRUE and FALSE into by hand behaves exactly like a column of checkboxes, and every formula below works on both.
Scenario: Take a list of your own, select a blank column beside it, and use Insert ▸ Checkbox. Tick three rows, then put =COUNTIF(E2:E20,TRUE) under the column and watch the number move as you click.
03SUM Over a Column of Ticks Is Always 0
=SUM(D2:D13) returns 0 on a column with six ticks in it. Not an error, not a warning, not a green triangle: zero. Logical values inside a reference are ignored by SUM, AVERAGE, COUNT, MIN and MAX.
Typed straight into a formula they count. =SUM(TRUE,TRUE) is 2, because an argument you write yourself is taken at face value. Read out of cells, the same two values are skipped — so the rule is about where the value comes from, not what it is.
=COUNT(D2:D13) returns 0 for the same reason: it counts numbers, and a logical value is not one. =COUNTA(D2:D13) returns 12, because it counts every cell that is not empty, ticked or not.
So the two functions anybody reaches for first answer 0 and 12 on a column whose honest answer is 6. Neither is wrong. Neither was asked the question.
04COUNTIF Counts the Ticks, SUMIFS Spends Them
=COUNTIF(D2:D13,TRUE) → 6
=COUNTIF(D2:D13,FALSE) → 3
=SUMIFS(C2:C13,D2:D13,TRUE) → 6,940.75
=COUNTIFS(D2:D13,TRUE,B2:B13,"Electrical") → 2
A criteria of TRUE with no quotes around it is the logical value, and the logical value is what a ticked box holds. That one formula is the whole of counting checkboxes.
SUMIFS takes the same criteria, so a cost column and a tick column give you the money behind the ticks in a single cell. Here that is £6,940.75 — correct arithmetic, over the six rows it is able to see.
The number is right and the register is wrong, which is the hardest kind of mistake to find. Nothing errors, nothing is blank, and the answer is a plausible fraction of a total nobody computed.
Percentages inherit it. =COUNTIF(D2:D13,TRUE)/COUNTA(D2:D13) reports 50% complete on a register that everybody in the room can see is three-quarters ticked.
Scenario: Put the formula count and the eye count side by side on your own register: =COUNTIF(D2:D13,TRUE) in one cell, the number of ticks you can see in the next. If they disagree, the gap is text.
05Where the Word TRUE Comes From
Nobody types it on purpose. It arrives three ways, and all three look exactly like a tick.
A formula with quotes in it. =IF(E2>=F2,"TRUE","FALSE") writes text, every time, because anything inside quotes is text. The version without them, =IF(E2>=F2,TRUE,FALSE), writes logical values — and =E2>=F2 on its own writes the same thing with less typing.
A paste. Type true into a cell and Excel parses it into the logical TRUE. Paste the same four characters out of a web page, an app or a report and they can land as text, because a paste is not a typed entry and nothing re-reads it.
A column formatted as Text. Format a column as Text — common on registers built from an import, to stop a plot reference being mangled — and everything entered into it stays text afterwards, the word TRUE included.
Scenario: Click one of the ticks in your register and look at the alignment. Logical values centre themselves; text sits left unless somebody changed it. Then type =ISTEXT(D5) beside the row you suspect.
06The Formulas That Refuse to Pretend
Three formulas on this sheet return an error instead of a number, and all three are doing you a favour.
=SUMPRODUCT(--D2:D13) → #VALUE! (6 on a clean column)
=FILTER(A2:A13,D2:D13) → #VALUE!
=IF(D5,"Signed","Open") → #VALUE! on a row holding the word
The double unary -- turns TRUE into 1 and FALSE into 0, which is the oldest way to count ticks and still the shortest. It cannot coerce a word into a number, so it stops rather than guessing.
FILTER wants logical values in its include argument and a word is not one. IF wants a condition it can read as true or false, and "TRUE" is neither of those things.
The errors are the honest half of the sheet. COUNTIF and SUMIFS skip what they cannot match and hand back a number; these three say so out loud. One cell reading #VALUE! has already told you more than a certificate reading £6,940.75.
Wrapping it away is the one thing not to do. =IFERROR(SUMPRODUCT(--D2:D13),0) turns a question into a zero, and the zero is what the next person builds on.
07Three Checks of One Cell Each
The arithmetic check. Entries, less the ticks, less the empty boxes:
=COUNTA(D2:D13)-COUNTIF(D2:D13,TRUE)-COUNTIF(D2:D13,FALSE) → 3
On a clean column that is 0. Here it is 3, and 3 is the number of cells holding something that is not a logical value at all.
The type check. =SUMPRODUCT(--ISTEXT(D2:D13)) returns 3 as well. It counts cells holding text whatever the text says, so it catches Yes, Y and a stray trailing space along with the word TRUE.
The sort. Sort the column A to Z. Excel orders numbers first, then text, then FALSE, then TRUE — so the text ticks land above every real one, and three identical-looking cells separate themselves in one click. Sort back on the plot column afterwards.
A fourth, free of charge: open the column's filter dropdown. A column holding both kinds of tick offers you TRUE twice.
Scenario: Run the arithmetic check under every tick, flag or Yes/No column in the workbook you trust most. It is one cell per column, it costs a minute, and the answer should be 0 everywhere.
Try it in the grid08Turning the Words Back Into Ticks
A helper column repairs it without guesswork:
=IF(ISTEXT(D2),UPPER(TRIM(D2))="TRUE",D2)
ISTEXT asks whether this is one of the bad cells. If it is, TRIM strips the spaces an export leaves behind, UPPER makes true and True the same thing, and the comparison returns a real logical value. If it is not, the original value passes through untouched.
Copy the helper column, Paste Special ▸ Values over the original, then apply Insert ▸ Checkbox to the column. Every row now holds a logical value and every row draws a box.
Prove it before you delete the helper. =SUMPRODUCT(--ISTEXT(D2:D13)) should return 0, and =COUNTIF(D2:D13,TRUE) should now return 9 — the number the register has been claiming all along.
Text to Columns ▸ Finish is worth a try on a copy of the column, since it re-parses entries the way typing does. Whichever route you take, finish on those two counts: a repair you have not counted is a hope.
Scenario: On a copy of your register, add the helper column, paste its values over the tick column, and run both proofs. Keep the helper formula in a cell comment or a notes sheet — the next export will need it again.
Try it in the grid09Total Rows, Pivots and Lookups
A tick column inside an Excel Table behaves itself, with one exception. The Total Row's Sum returns 0, because it is a SUBTOTAL and SUBTOTAL ignores logical values exactly as SUM does. Choose Count instead, or write the SUMIFS yourself.
A PivotTable treats the column as non-numeric and offers Count, which counts ticked and unticked rows alike. Filter the field to TRUE, or add a numeric helper column of =--D2 and sum that.
Pulling a tick onto another sheet is an ordinary lookup:
=XLOOKUP(A2,Register!$A$2:$A$13,Register!$D$2:$D$13,FALSE)
The fourth argument is a decision, not a default: a plot missing from the register is being reported as not signed off. If missing should be louder than unticked, return "NOT ON REGISTER" instead and let the handover sheet show it.
Two more cells worth keeping. =AVERAGE(--D2:D13) is the signed-off fraction — 0.75 once the register is clean, and #VALUE! while a word is still in it. And one gate for the pack:
=AND(COUNTIF(D2:D13,FALSE)=0,SUMPRODUCT(--ISTEXT(D2:D13))=0)
That is TRUE only when every plot is signed off and every tick is a tick. A report that cannot mislead is one LET away:
=LET(ticks,D2:D13,words,SUMPRODUCT(--ISTEXT(ticks)),
IF(words>0,"CHECK "&words&" TEXT TICKS",SUMIFS(C2:C13,ticks,TRUE)))
10Eight Things That Bite
=SUMover a tick column. Returns 0 with six ticks in it, and 0 is a number a report will happily print.=COUNTAas a count of ticks. It counts the unticked boxes too — 12 on a register carrying 9 ticks.=IF(E2>=F2,"TRUE","FALSE"). The quotes make text, and the text matches no criteria of TRUE anywhere in the workbook.- A tick column formatted as Text. Everything entered stays a word, so the column fills with impostors one row at a time.
- Form Control checkboxes on a list you sort. The values move with the rows and the boxes do not, and nothing says which box belongs to which plot now.
IFERRORaround a coercion.--andFILTERerror for a reason; wrapping them to 0 throws the reason away and keeps the number.- A Table Total Row set to Sum.
SUBTOTALignores logical values the waySUMdoes, so the total under a ticked column reads 0. - Counting the ticks by eye. Nine on screen, six in the formula, and the only place the difference appears is a number nobody was asked to produce.
11Mini Exercises
- Build the register's three totals in three cells: everything,
=SUM(C2:C13); everything ticked, by hand; everything a criteria of TRUE can reach,=SUMIFS(C2:C13,D2:D13,TRUE). Expect £31,972.00, £16,736.50 and £6,940.75. - Put
=COUNTA(D2:D13)-COUNTIF(D2:D13,TRUE)-COUNTIF(D2:D13,FALSE)under the tick column, then retype one of the three bad cells as a real tick and watch the check fall to 2. - Write
=COUNTIFS(D2:D13,TRUE,B2:B13,"Electrical")and then count the Electrical sign-offs by eye. The difference is one plot and £4,120.00. - Repair the column with the helper formula, paste the values over it, and prove the repair twice:
=SUMPRODUCT(--ISTEXT(D2:D13))at 0 and=COUNTIF(D2:D13,TRUE)at 9.
What to Take Away
A checkbox is a logical value wearing a box. Everything that is strange about counting checkboxes is just the way Excel has always handled TRUE and FALSE, which is to ignore them inside a reference and to match them only against a criteria of the same type.
So count them with COUNTIF, spend them with SUMIFS, coerce them with -- when you want arithmetic, and never reach for SUM. Those four habits cover every tick column you will ever meet.
And check the type, not the display. The word TRUE and the value TRUE look identical, land in the same column, and differ only in what a formula can do with them — which is the difference between £16,736.50 and £6,940.75 on a certificate that nobody had any reason to doubt.