Excel guide · 3 min read

#NULL! error in Excel: why it happens and how to fix it

Q: How do I sum two non-adjacent ranges?

Separate them with a comma: =SUM(B2:B10,D2:D10). You can list up to 255 ranges in SUM this way.

Quick answer#NULL! appears when a formula uses a space between two ranges that do not overlap. In Excel a space is the intersection operator, so =SUM(B2:B4 D2:D4) asks for the cells the two ranges share, and there are none. Replace the space with a comma to add both ranges, or with a colon to make one continuous range.

Sample data

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

	A	B	C	D
1	Week	Store A	Store B	Store C
2	Week 1	4200	3850	5100
3	Week 2	3975	4010	4890
4	Week 3	4410	3720	5230

What the space actually does

Excel has three range operators. A colon (B2:B4) means "from here to there". A comma (B2:B4,D2:D4) means "this and also that". A single space means "only the cells in both". That last one is called the intersection operator, and it is the source of nearly every #NULL! error.

So =SUM(B2:B4 D2:D4) is not adding two columns. It asks for the overlap between column B and column D, which is empty, and Excel reports that with #NULL!.

Fix 1: add two separate ranges with a comma

If you meant to total Store A and Store C, separate the two ranges with a comma. SUM, AVERAGE, COUNT, MAX and MIN all accept several ranges this way.

=SUM(B2:B4,D2:D4)

Result: 27805

Store A (12,585) plus Store C (15,220) gives 27,805.

Fix 2: one block of cells with a colon

If you meant every store for every week, you wanted one continuous range. A typo such as =SUM(B2 D4), with a space instead of a colon, also returns #NULL!. Use the top-left and bottom-right corners joined by a colon.

=SUM(B2:D4)

Result: 39385

All nine weekly figures across the three stores add up to 39,385.

When a space is correct: real intersections

The intersection operator is useful when the ranges do overlap. =C2:C4 A3:D3 returns the single cell both ranges share, C3, which is Store B in Week 2 (4,010). With named ranges it reads almost like English: =Week_2 Store_B.

If you inherit a file that uses this on purpose, leave the space alone. If the ranges no longer overlap (someone moved a column), you get #NULL!, and the fix is to repair the ranges, not to delete the space.

=INDEX(B2:D4,2,2)

Result: 4010

INDEX picks the same cell more explicitly: row 2, column 2 of the table is Store B in Week 2, 4,010.

Find the stray space fast

Click the cell and look in the formula bar for a space between two cell references. In long formulas, press F2 and step through it, or use Formulas > Evaluate Formula to watch Excel reach the #NULL! step.

Select the cell showing #NULL!.
Go to Formulas > Evaluate Formula.
Click Evaluate repeatedly; the step that turns into #NULL! contains the bad space.
Replace that space with a comma or colon and press Enter.

Common mistakes

Typing a space after a comma inside a range list

=SUM(B2:B4, D2:D4) is fine because the comma separates them. The problem is a space with no comma, such as =SUM(B2:B4 D2:D4).

Hiding #NULL! with IFERROR

The formula is wrong, not the data. IFERROR would show 0 or blank where a real total should be. Fix the operator.

Assuming another language version is broken

In some European versions of Excel the argument separator is a semicolon, not a comma. =SUM(B2:B4;D2:D4) is correct there, and a comma may be read differently.

FAQ

What does #NULL! mean in Excel?

It means the formula asked for the intersection of two ranges that do not overlap. In practice it is almost always a space typed where a comma or colon should be.

What is the intersection operator in Excel?

A single space between two references. It returns only the cells that belong to both ranges, for example =B2:D4 C1:C9 returns cells C2:C4.

How do I sum two non-adjacent ranges?

Separate them with a comma: =SUM(B2:B10,D2:D10). You can list up to 255 ranges in SUM this way.

Functions used

Related guides

Updated October 31, 2026