The rarest of the errors, and nearly always a typing slip: a space between two ranges where a comma or a colon was meant.
Why it happens
A space between two references is an operator in Excel: it asks for the cells the two ranges have in common. When they share none, the answer is #NULL!.
SUM(A1:A5 C1:C5) is the classic case. The space asks for the overlap of two columns that never meet. SUM(A1:A5,C1:C5) adds them both.
Because the space is legitimate syntax, Excel does not flag it as a mistake. It simply answers the question that was asked.
Questions
Is a space ever useful?
Occasionally, with named ranges: Sales Q1 returns the cell where the two named blocks cross. It is a deliberate technique, rarely typed by accident on purpose.
How do I spot it?
Look for a space between two references inside the brackets. Replacing it with a comma is usually the whole fix.
Why does my sum work in one place and not another?
The ranges may happen to overlap in one sheet and not the other, so the same slip only shows up sometimes.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
