ExcelTutorials

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

3 Mins read

Indirect Function in Excel returns a reference to a range. Excel Indirect functions are useful to create a reference that won’t change if columns or rows are inserted in a worksheet. There are a number of indirect functions in excel such as SUM, MAX, MIN & Independent Cell Value. In this tutorial, we explain one way to use these indirect functions. To learn more ways, please check our latest Excel Ninja Training with Certification. indirect function in excel

Excel Vlookup formula – Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

What is an INDIRECT Function in Excel?

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.

How INDIRECT Function in Excel 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 the 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
Independent cell value in Indirect function

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

INDIRECT Formula to find a 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

Excel Vlookup formula – Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

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)

15 Excel Data Cleaning Tricks Guidebook

Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages) 

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 the 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.

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 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″))

15 Pivot Tables Tricks for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.

Most Popular Tricks are #3, #7 & #12

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…