Level 5 · What "<>" does with blank cells

Excel’s “not equal” criterion looks simple: "<>BFCM25", everything except BFCM25. The trap is what “everything” includes. On a column that is mostly empty, the answer is usually not what the person asking had in mind.

”<>” includes blank cells

COUNTIF, COUNTIFS, SUMIFS and the rest test each cell in the range one by one. For "<>BFCM25", the test is “this cell is not BFCM25”. An empty cell passes it. On Sunday’s orders (the sheet from the last level) column D holds 25 BFCM25 codes and 33 empty cells:

Formula	Result	What it counts
=COUNTIF(D2:D59,"BFCM25")	25	orders with BFCM25
=COUNTIF(D2:D59,"<>BFCM25")	33	everything else, blanks included
=COUNTIF(D2:D59,"")	33	empty cells
=COUNTIF(D2:D59,"*")	25	cells holding any text

25 + 33 = 58, the number of orders. The "<>" count is simply “all rows minus the BFCM25 rows”.

Counting only the filled-in cells

Chloe’s real question is “how many orders used another coupon?” That needs two tests on the same column: not BFCM25, and not empty. A bare "<>" means “not equal to nothing”, so it keeps only filled-in cells:

=COUNTIFS(D2:D59,"<>BFCM25",D2:D59,"<>")

This returns 0: everyone who used a coupon on Sunday used BFCM25. Another way to write the same thing is “coupon orders minus BFCM25 orders”: =COUNTIF(D2:D59,"*")-COUNTIF(D2:D59,"BFCM25"), also 0.

The same trap with statuses and sums

The rule is the same for every function in the family. =SUMIF(C2:C59,"<>refunded",E2:E59) adds up every row that isn’t refunded, which includes cancelled orders and any row whose status was left empty. On a clean export like this one, statuses are never blank, but a cancelled order still slips in: the formula gives 20,961.35, while revenue from completed orders is 17,756.51. “Not refunded” and “completed” are different questions.

Excel versus SQL

If you also write SQL, note that the two tools disagree. In SQL, WHERE coupon_code <> 'BFCM25' drops the rows where coupon_code is NULL, because comparing NULL gives “unknown”. In Excel, "<>BFCM25" keeps the empty cells. Same words, opposite results, so check how blanks are handled whenever you move a metric from one tool to the other.

Common mistakes

• Trusting a “not” count on an optional column. Check it against "*" or "" counts first.
• Cells that look empty but are not. A cell holding a single space is text: "*" counts it and "" does not. If counts don’t add up, look for spaces.
• Writing "<>"&"". It works, but "<>" alone already means “not empty”.