Cause: The formula contains an incorrect range operator.
Solution: To refer to a range of adjacent cells, use a colon ( : ) to separate the reference to the first cell in the range from the reference to the last cell in the range.
For example, =SUM(A1:A10) refers to the range from cell A1 through cell A10.
Solution: To refer to two areas that don't intersect, use the union operator, the comma ( , ).
For example, if the formula sums two ranges, make sure that a comma separates the two ranges as in the following example, =SUM(A1:A10,C1:C10).
Cause: The formula contains references to named ranges that do not intersect.
Solution: Change the range references so that they intersect.

On the Formulas tab, under Function, click Formula Builder.

In the Arguments section of the Formula Builder, click any argument. Excel highlights each cell or range of cells that are referenced by the formula with a different color.
Color codes show that the formula in cell D9 refers to cells B9 and C9

Do one of the following:
To 
Do this 
Move a cell or range reference to a different cell or range 
Drag the colorcoded border of the cell or range to the new cell or range. 
Include more or fewer cells in a reference 
Use the drag handle in the lowerright corner of the border to adjust the range. 
Enter a new reference 
In the Formula Builder, select the argument that contains the reference, and then type a new one. 

Press RETURN .
Cause: The formula contains references to unnamed ranges that do not intersect.
Solution: Change the range references so that they intersect.

On the Formulas tab, under Function, click Formula Builder.

In the Arguments section of the Formula Builder, click any argument. Excel highlights each cell or range of cells that are referenced by the formula with a different color.
Color codes show that the formula in cell D9 refers to cells B9 and C9

Do one of the following:
To 
Do this 
Replace references with names 
Select the range of cells that contains formulas in which you want to make replacements. 
Include more or fewer cells in a reference 
Use the drag handle in the lowerright corner of the border to adjust the range. 
Change the references to names in all formulas on the sheet 
Select any cell on the sheet. 

On the Insert menu, point to Name, and then click Apply.

In the Apply names box, click one or more names.