O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #80. Hack One of Excel's Database Functions to Take the Place of Many Functions

Excel's database functions—DSUM, DCOUNT, etc.—can take the place of potentially thousands of functions, thereby reducing both recalculation time and workbook space.

When using Excel's database functions, you can specify up to 256 different criteria. You might, for example, want to sum amounts in column A where the corresponding amount in column B is greater than 100 and the corresponding age in column C is less than 40. If, however, you want to sum amounts where corresponding amounts in column B are less than 50, you need to use another function and a different range of criteria. It would much easier if you had a single function and could easily and quickly change the criteria! If you have never used Excel's database functions before, we strongly recommend that you familiarize yourself with them, as they are very good for extracting statistical information from an Excel database or table.

To see how this works, set up your data as shown in Figure 6-18. Keep the column headings the same, but the data that resides in it can be any fictitious data. Name this table of data, including all column headings, AllData. Name the sheet Data.

Proposed data

Figure 6-18. Proposed data

Insert another worksheet and call this worksheet Results. In cell A2, enter the following formula:

=Data!A1

Copy this across to cell F2 so that you ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required