Using Intersections of Named Ranges in Excel Formulas

ID: Q61891

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 "Other" 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:


   =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:


   =rent food

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

REFERENCES

"Online Help," version 5.0
"User's Guide 1," version 4.0, pages 133, 138
"User's Guide," version 3.0 for Windows, page 114, 120-121
"User's Guide," version 3.0 for the Macintosh, page 108, 114-115

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 5.0

Keywords :
Version :
Platform :
Issue type :