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.

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.

Next, month 2 is selected, for February in cell I2.

Finally, the Clothing item is selected in cell J2.

This formula in cell K2 returns Sales of \$13,756:
`=SUMPRODUCT((YEAR(Activity)=H2)+0,(MONTH(Activity)=I2)+0,INDIRECT(J2))`

Verifying the formula, here is the range for Clothing sales in February 2015.

Posted in Tom's Tutorials for Excel

5 comments on “Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function.”
1. Yaseen says:

• Tom Urtis says:

Thank you! Many more Excel tips and examples coming in 2018.

2. Catarino says:

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

• Tom Urtis says:

Thank you! No need to reciprocate, I enjoy sharing these examples.

3. sandeep kothari says:

With INDIRECT, you are helping us DIRECTLY.