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.