Microsoft KB Archive/41622

-

{| 2.x 3.00 4.00 4.00a 5.00 | 2.20 2.21 3.00 WINDOWS                 | OS/2 kbusage The information in this article applies to:
 * width="100%"|


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0

SUMMARY
Below is an example of calculating a sum of the intersection of a column and name defined to be a noncontiguous area on a sheet. In this example, the name COST will be defined to be A1:C1, A3:C3, A5:C5, as shown below. This is an area that is noncontiguous, that is there are rows within the area that are not included in the area. In this example, those rows would be row 2 and row 4. The example is as follows:

A1: 1  B1:   4  C1:  7           <<--| A2: 98 B2:  99  C2: 67               | A3: 2  B3:   5  C3:  8           <<--|<--COST A4: 35 B4:  76  C4: 85               | A5: 3  B5:   6  C5:  9           <<--|

MORE INFORMATION
To sum up the Total of Cost, the function involved would be as follows:

=SUM(COST) This sum would return the value 45. To find the Sum of Cost that falls in column A, the function would be as follows:

=SUM(COST A:A) Note that there is a blank space between COST and A:A. This space is the intersection character. It creates a new reference by finding what is common between the two references used before and after it. In this example, cells A1, A3, and A5 are common to both column A and the defined name COST. This sum would then return the value 6. To find the Sum of Cost that falls in columns A and C, the function would be as follows:

=SUM(COST A:A, COST C:C) This sum would return the value 30.