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.
Hope to learn more from you sir
Thank you! Many more Excel tips and examples coming in 2018.
I do not know how I can reciprocate your unselfish sharing of Excel expertise.
Thank you! No need to reciprocate, I enjoy sharing these examples.
With INDIRECT, you are helping us DIRECTLY.