INDIRECT Function with SUM, MAX, MIN & Independent Cell Value

What is an INDIRECT Function?

The INDIRECT Function in Excel helps you to return a reference to a range. Even when you insert Rows or Columns in a Worksheet the reference you created won’t change. This will help you to create a reference from letters and numbers in other cells.

Downlaods

How INDIRECT Function Works with an Example

The INDIRECT function is useful when you want to return a value, based on a text string. Example: You’ll be able to see the list of food items and their Consumption by Countries such as USA, China and Japan. The quantity is in Million i.e. 1 Million=10,00,000

Problem: You need to find out:

  • An independent Cell Value
  • SUM consumption of the Food items of USA
  • MAX Value of Food Consumption of Japan
  • MIN Value of Food Consumption of CHINA

Question: How will you find the Values?

Solution: INDIRECT Formula will help you get this in minutes

INDIRECT Formula to find an independent Cell Value

  1. In the Cell F3, Type =INDIRECT(for ref_text give the reference of the above Cell, Select F2) i.e. =INDIRECT(F2)
  2. You get #REF! (This is because there was no Cell Value assigned to the Cell F2)
  3. Select C5 and Enter C5 as the Cell Value at the place of F2. You get Value 150

(The Error text disappears and you get the Value 150 i.e. the Value of Silk Consumption in China)

INDIRECT Formula to find an Sum consumption

  • Select the range of Cells for the Columns USA, CHINA and JAPAN
  • Provide a Table Name to USA, CHINA and JAPAN respectively in the NAME BOX
  • Type the name USA in the Cell F2
  • In the Cell F3, Type =SUM(INDIRECT(F2))

Sum Value

Note 1: Don’t forget to close the Bracket for SUM & INDIRECT both

Note 2: If you don’t Select the Table Range and Name your Table then Typing USA won’t give the SUM in Excel. You must Give your Table a name in the NAME BOX

  • Press Enter. You’ll get the Value 1250

(You can Cross Check the Total by Calculating the Total Values of USA. The Answer remains same)

Note: You can change the Country Name to China and Japan Respectively to get the Sum of the Country

INDIRECT Formula to find MAX Value

  • In the Cell F2, Type JAPAN
  • In the Cell F3, Type =MAX(=INDIRECT(F2))
  • Press Enter. You’ll get the Value 450

Max Value in Indirect Function in Excel

Note: If you don’t Select the Table Range and Name your Table then Typing USA won’t give the SUM in Excel. You must give your Table a name in the NAME BOX

(You can Cross Check the MAX Value by Checking out the Column of Japan. The Answer remains same)

INDIRECT Formula to find MIN Value

  • In the Cell F2, Type CHINA
  • In the Cell F3, Type =MIN(=INDIRECT(F2))
  • Press Enter. You’ll get the Value 150

Min Value in Excel

Note: If you don’t Select the Table Range and Name your Table then Typing USA won’t give the SUM in Excel. You must give your Table a name in the NAME BOX

(You can Cross Check the MIN Value by Checking out the Column of Japan. The Answer remains same)

Wish to learn about:

‘Excel reference cell in another sheet dynamically’?

Excel INDIRECT function – syntax and basic uses

As you understand from the Word, ‘Excel INDIRECT’, it helps to indirectly reference cells, ranges, other sheets or workbooks.

In other words, The INDIRECT function in Excel makes it easy for you to create an Excel dynamic cell reference or range reference. You do not have to do the “hard coding” of the references into your Excel formulas.

This results in the change of a Cell Reference within a formula without changing the formula itself. More importantly, these indirect references in the INDIRECT Formula in Excel do not change when a new row or column is added or inserted in the worksheet. Also, the Cell Reference remains same when you delete the existing Rows and Columns. When some formulas get extended you can use wrap text in Excel

If you wish to Write an INDIRECT Function in Excel, you need to understand the Basics of Function Arguments.

Download Sample File

What if your Indirect formula not working?

Situation: You’re using the indirect formula in Excel to pull data of other tabs.

These tab names are compiled and placed on a tab through VBA code (this is a clear cut and paste)

Problem: The Indirect formula is unable to calculate because what I am assuming is the format of the data which is present in the column B

It is observed that: Whenever you type over the data (which is exactly the same) this excel formula works.

Solution: Before you overwrite, you need to check:

  • If there are unseen Whitespace Characters in the Cell
  • If something is Misspelled

If this doesn’t work then, you can try this:

=IF(ISERROR(INDIRECT(TRIM($B2)&”!U2″)),””,INDIRECT(TRIM($B2)&”!U2″))

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
Insert Symbol Dialog Box to Insert or Type degree symbol in Excel
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.