Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function.

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function
The INDIRECT function can refer to a named range for quick data analysis, especially if you don’t need or want to use a pivot table. In this example, columns B:F hold several years of daily sales activity for a department store. The next picture shows a list of named ranges in the name box that correspond to the header labels.
TTFE7a
The cells in range H2:J2 are data validated. Suppose you want to see historical data for Clothing sales in February 2015. In the next picture, year 2015 is selected in cell H2.
TTFE7b
Next, month 2 is selected, for February in cell I2.
TTFE7c
Finally, the Clothing item is selected in cell J2.
TTFE7d
This formula in cell K2 returns Sales of $13,756:
=SUMPRODUCT((YEAR(Activity)=H2)+0,(MONTH(Activity)=I2)+0,INDIRECT(J2))
TTFE7e
Verifying the formula, here is the range for Clothing sales in February 2015.
TTFE7f

Share Button
Posted in Tom's Tutorials for Excel
Tags: , ,
5 comments on “Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function.
  1. Yaseen says:

    Hope to learn more from you sir

  2. Catarino says:

    I do not know how I can reciprocate your unselfish sharing of Excel expertise.

  3. sandeep kothari says:

    With INDIRECT, you are helping us DIRECTLY.

Leave a Reply

Your email address will not be published. Required fields are marked *

*