Browse

XOR in Zoho Sheet: Find exactly one exception

Logical Intermediate Zoho Sheet

Understand odd-parity XOR in Zoho Sheet: exactly one of two conditions, multiple conditions, examples and mistakes.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Zoho Sheet. How formula examples are checked

Quick answer: Use XOR when the number of TRUE conditions should be odd. With two tests it flags exactly one true condition, but with three or more tests XOR does not mean exactly one: 3 TRUE tests also yield TRUE.

Official syntax and parameters

=XOR(logic1; [logic2]...)
Argument Definition Example
logic1 First required logical expression. C3="Pending"
logic2, more Optional further logical expressions; XOR is TRUE when the total count of TRUE inputs is odd. F3=0

Zoho’s reference uses an initial required argument and optional additional arguments, and treats numeric zero as FALSE/nonzero as TRUE. Text is ignored.

Practice dataset: eight orders

In a new Zoho Sheet, put these headings in A1:F1 and enter the eight rows exactly into A2:F9. Set D and F to numbers, not text; use an empty column such as H for formulas so you do not overwrite the input table. A complete CSV copy is included with this review batch; you can also type the records directly.

Row A: Order ID B: Region C: Status D: Amount E: Agent F: Units
2 O-101 East Paid 120 Ana 12
3 O-102 West Pending 75 Bo 5
4 O-103 East Paid 210 Ana 7
5 O-104 North Paid 90 Cy 0
6 O-105 West Paid 150 Bo 3
7 O-106 East Pending 60 Dana 8
8 O-107 West Paid 125 Ana 12
9 O-108 North Pending 80 Cy 4

When copying examples, keep the semicolons shown in Zoho’s English-language function help. Other spreadsheet products and locale settings may show commas instead. Cell addresses in the examples refer to the table on this sheet, not to a different workbook.

Reproducible examples

Example 1 — Exactly one of two order exceptions

Test whether an order is Pending or has zero units, but not both:

=XOR(C3="Pending";F3=0)

Expected TRUE for row 3 (O-102): Pending is TRUE; five units means zero-stock is FALSE; exactly one TRUE.

=XOR(C5="Pending";F5=0)

Expected TRUE for row 5 (O-104): the order is Paid (FALSE for Pending) but units are zero (TRUE).

=XOR(C2="Pending";F2=0)

Expected FALSE for row 2 (O-101): Paid with 12 units, so neither condition is true.

Example 2 — Both tests true is FALSE

=XOR(C3="Pending";D3<100)

Expected FALSE. Row 3 is Pending and the amount is 75, so two TRUE tests give an even count; XOR returns FALSE. This is a common source of surprise if you expected OR.

Advanced application — parity versus an exact-one rule

An order-review workflow can use XOR to flag a single exception:

=IF(XOR(C5="Pending";F5=0);"Exactly one issue";"Both or neither")

Expected: Exactly one issue for O-104. The first test is false and the second true.

To see the odd-number rule clearly, compare:

=XOR(TRUE;TRUE;TRUE)

Expected TRUE (three TRUE arguments). And:

=XOR(TRUE;TRUE;FALSE)

Expected FALSE (two TRUE arguments). If your business rule is exactly one of three conditions, count the TRUE conditions explicitly and compare that count with one; XOR alone would also accept three. For example, for explicitly numeric 0/1 flags in H2:J2 you could use =SUM(H2:J2)=1, but ensure those are numeric indicator columns, not unreviewed strings.

Errors, limitations and troubleshooting

  • Do not equate XOR and OR. OR returns TRUE for one or more TRUE conditions; XOR requires an odd count of TRUE conditions.
  • Two-argument versus many-argument meaning: With two comparisons it means exactly one; with four comparisons one or three TRUE tests qualify.
  • Text arguments: Zoho says all text values are ignored. Use logical comparisons on text fields, for instance C3="Pending", rather than passing the raw text cell C3.
  • Numeric coercion: Zero is FALSE; any nonzero numeric argument is TRUE under Zoho’s documented rules.
  • #NAME!: Check function spelling, comparison operators, quotes around labels and reference delimiters.
  • #VALUE!: Inspect types and errors coming from expressions or referenced cells. The official reference lists this error for invalid data types.
  • #REF!: Repair missing or moved references before evaluating XOR.
  • Unexpected FALSE: Count how many input expressions are TRUE. If 0, 2 or 4 are TRUE, FALSE is the expected XOR result, not a calculation fault.

Cross-spreadsheet compatibility

Microsoft Excel and Google Sheets also define logical XOR in terms of an odd count of TRUE inputs; see Microsoft’s XOR reference and Google’s XOR documentation. However, argument separators, how text inputs are treated, arrays and availability in older releases differ by product and version. Zoho’s documented examples use semicolons. Do not assume native parity was tested just because these three references describe the same Boolean operation.

Official reference and verification

Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Zoho Sheet.

  • No genuine Zoho Sheet screenshots are included; this guide does not use mock application images.