Use Natural Language Formulas Without Using Natural Language Formulas

February 24, 2021 - by Bill Jelen

Challenge: Excel 2003 offers relatively obscure natural language formulas, but they were removed from Excel 2007. The table nomenclature in Excel 2007 isn’t as easy to use.

Solution: To solve this problem, you can use the intersection character in your SUM function. Everyone knows that =SUM(A2:A10) sums the nine-cell range from A2 through A10. Most people realize that =SUM(A1,A3,A5,A7,A9) adds up the five cells specified. However, very few people understand that the space character is actually an intersection operator when used in a SUM function!

Say that you have the worksheet shown in Figure 40. As discussed in “Quickly Create Many Range Names” on page 76, you can add range names by using these steps:

Select the range A1:F13.
Use Insert, Name, Create (in Excel 2003) or Formulas, Create from Selection (in Excel 2007).
In the Create Names or Create Names from Selection dialog, select Top Row and Left Column. Click OK. Excel creates names for the 12 cities in column A. For example, the name Louisville applies to cells B13:F13. Excel also creates five range names for the headings in row 1. For example, the GP range refers to D2:D13.

You can now easily sum a range by using =SUM(Sales) or =SUM(Atlanta,Charlotte). However, if you include a space between the named ranges, Excel includes only the cells at the intersection of the two ranges. The formula =SUM (Boston COGS) finds the one cell at the intersection of the Boston range (B3:F3) and the COGS range (C2:C13). Only one cell is in common between these two ranges, so the result is the 88,351 found in cell C3.

Figure 40. Excel creates five named ranges in this selection.

Summary: When you use a space between arguments in a SUM function, Excel returns only the intersection of the ranges.

Title Photo: Ivan Shilov at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.