How to Set Excel Ranges with INDIRECT and SUMIFS

Let’s explore how to set Excel ranges using the INDIRECT function. In this example, we will find and total specific items across multiple worksheets based on specified criteria.

Learn about the Excel range-setting feature.

CONTENTS

Functions Needed to Understand Excel Range References

The following two functions are used to calculate totals for items across multiple worksheets.

Excel Range Reference Example

In an Excel file where sales quantities by day are stored on separate worksheets as shown below, we will create a summary worksheet for calculating totals based on the desired criteria and use Excel range references to automate the process.

To demonstrate Excel range references, we will use a file with sales data by day on each worksheet.

Create a New Worksheet for Multi-Sheet Range References

Create a new worksheet with the desired name to connect ranges across multiple Excel worksheets.

Create a new worksheet to define ranges across multiple worksheets.

Create a table that makes it easy to retrieve and analyze values from the desired cells across multiple worksheets.

A data table was created to connect and analyze values from multiple worksheets.

In the table above, the Seller and Product fields are set up as selectable lists.

What Is the Excel List Feature?

The list feature was created using data validation.

Functions Used (INDIRECT, SUMIFS)

The following formula is used to automatically retrieve values from other worksheets for each day.

The formula below was used to automatically retrieve the desired values even when worksheet values change.

To automate links to values across multiple Excel worksheets with one formula, the following formula uses INDIRECT.

In cell D5, the formula for calculating the total Monday sales quantity for Seller Kevin and Product jersey is as follows.

=SUMIFS(MON!C:C,MON!A:A,’SUMIFS&INDIRECT’!B5,MON!B:B,’SUMIFS&INDIRECT’!C5)

Excel data automation was implemented with the SUMIFS function using the formula below.

To total the sales quantities on the “MON” worksheet, select the MON!C:C column and use the seller and product fields as criteria.

The column to be totaled on an individual Excel worksheet is explained.

Starting in cell E5, use the following formula with the INDIRECT function to define an automated range.

=SUMIFS(INDIRECT(“‘”&D4&”‘”&”!$C:$C”),INDIRECT(“‘”&D4&”‘!$A:$A”),’SUMIFS&INDIRECT’!$B5,INDIRECT(“‘”&D4&”‘!$B:$B”),’SUMIFS&INDIRECT’!$C5)

To turn the day labels in the table below into formulas that activate the range on each corresponding day worksheet, make the following change.

The core concept of the Excel range-setting feature is explained.

[INDIRECT(“‘“&D4&”‘“&”!$C:$C“)] is the key part of the formula that uses the text TUE, the value in cell D4, as a worksheet range reference.

The formula above functions as a range reference that exactly matches TUE!C:C.

Conclusion

The INDIRECT function is one of the functions used in Excel. It retrieves a cell value by referring to a cell address or range name written as text.

Setting ranges is very important in Excel. Proper range references make data analysis and processing easier and faster. For example, if ranges are not set correctly, processing data can take longer or may not produce the desired results. Therefore, when setting a range, you must select an appropriate range based on the size and location of the data. To do this, use appropriate functions and range-setting techniques according to the data structure and purpose. You must also set ranges correctly when using the INDIRECT function.

Because the INDIRECT function retrieves a cell value by referring to a cell address or range name written as text, incorrect range settings can produce unwanted results. Therefore, define ranges accurately when using the INDIRECT function.

Related Functions

How to Create a Dynamic Range in Excel