Indirect References to a DSUM Parameter

Summary: Indirect references can be very helpful in formulas, but getting your head around how they work can sometimes be confusing. Here's an example of using an indirect reference within another formula. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Octavio has a worksheet that has a lot of named ranges in it. In one section of his worksheet he has a list of those names. In a formula that uses the DSUM function, Octavio wants to use different cells in this list to refer to the actual "database" that is used by the function. For instance, if "February09" is a named range and cell F12 contains the text "February09," Octavio wants to specify F12 as the first parameter in the DSUM function and have it get the actual range. When he tries the following, where Criteria is a named range for the summation criteria, he gets an error:

=DSUM(F12, "Profit", Criteria)

The solution to this is to use, instead of the actual cell, the results of the INDIRECT function. This function grabs whatever is at the cell it references, and then uses that content as a "pointer" to another cell or range. Thus, the following two formulas provide the exact same result:

=DSUM(INDIRECT(F12), "Profit", Criteria)
=DSUM(February09, "Profit", Criteria)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2885) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Remove Some Stress at Tax Time! Doing your personal income taxes can be a royal pain. Why not make the process just a bit less stressful with our 101-question checklist. You can prepare for filing your taxes with confidence, knowing you've covered all your bases.

Check out Filing Your Income Taxes Checklist today!

Helpful Links

Newest Tips

Subscribe

RSS Feeds

Indirect References to a DSUM Parameter