Microsoft KB Archive/61891

{|
 * width="100%"|

Using Intersections of Named Ranges in Excel Formulas

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0
 * Microsoft Excel for OS/2, version 2.2, 3.0

-

SUMMARY
Microsoft Excel can determine the intersection of named ranges and use it in a formula.

MORE INFORMATION
The following is an example of how to determine the intersection of two named ranges.

  Type in the following data:

     A1:             B1: Jan   C1: Feb   D1: Mar A2: Rent       B2: 300   C2: 300   D2: 325 A3: Food       B3: 100   C3: 115   D3: 105 A4: Utilities  B4: 120   C4: 130   D4: 105 A5: Other      B5:  75   C5: 115   D5:  95  Select cells A1:D5. From the Formula menu, choose Create Names. Check Top Row and Left Column. Choose OK.

Seven new names have been created from the row and column headings. They refer to their respective rows and columns. For example, the name Rent refers to $B$2:$D$2 and Jan refers to $B$2:$B$5. Type the following formula in cell A7:

=Feb Food

The result that will be displayed is 115. This is the intersection of Feb with Food in the table.

Other Examples
To use the SUM function to return the sum of the range of values for January through March, excluding the &quot;Other&quot; category, use the following formula:

  =SUM((Jan Rent):(Mar Utilities))

The result is 1600.

To return the sum of only the two cells represented by (Jan Rent) and (Mar Utilities), use the following formula:

<pre class="FIXEDTEXT">  =SUM(Jan Rent,Mar Util)

The result is 405.

Microsoft Excel requires the parentheses around each intersection to indicate that the intersections (indicated by the space operator) are to be performed before determining the range (indicated by the colon).

The #NULL error value results when the two references have no cells in common. Consider the following formula in relation to the above table:

<pre class="FIXEDTEXT">  =rent food

The result is #NULL because these two ranges do not intersect each other.