• Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
    • Microsoft Visual
    • Microsoft Project
    • Microsoft Visio
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

How to Use INDIRECT with SUM to Create Dynamic Sum Formulas

0 Comments

By use INDIRECT with the SUM function, you can easily create dynamic sum formulas that refer to cell ranges specified as text. This allows you to avoid changing the reference from the formula multiple times.

INDIRECT 1

Use INDIRECT, you can create a cell or range reference by entering text into it. Here are the steps to combine use INDIRECT with SUM:

  1. Enter the cell range you want to refer to in a cell.
  2. In a different cell, enter the SUM function.
  3. Next, use the INDIRECT function and refer to the cell with the range address in the first argument of use INDIRECT.
  4. Close both functions and hit enter to get the result.INDIRECT 2

When you hit enter, the formula will return the sum of the values from the specified range, such as A2:A7.INDIRECT 3

Using INDIRECT to Sum Data from Another Sheet

If the range you want to sum is on a different sheet, you can still use INDIRECT and SUM.INDIRECT 4 For instance, you can enter the formula in “Sheet2” and refer to the range from the “Data” sheet. In the formula, enter the sheet’s name in one cell and the range itself in another cell. Then use this formula:

=SUM(INDIRECT(A1&”!”&B1))

Using INDIRECT to Sum Data from Multiple Sheets

If you want to sum values from a range of multiple sheets, you need to use a formula that employs SUMPRODUCT and SUMIF instead of SUM. Here’s an example formula:INDIRECT 5

=SUMPRODUCT(SUMIF(INDIRECT(“‘”&A2:A4&”‘!”&B2),”>0″))

In this formula, INDIRECT refers to the name of the sheets and the range, creating a 3D-Range for the A2:A7 range in three sheets (Data1, Data2, and Data3). INDIRECT 6Then, SUMIF uses this range and returns individual sums from all three ranges. INDIRECT 7Finally, SUMPRODUCT adds up the values and returns a single sum value in the cell.INDIRECT 8

To learn more about using SUMPRODUCT IF and its usage, you can refer to this guide.

Rate this post
34
272 Views
Tips for quickly filling values into blank cells in an Excel spreadsheetPrevTips for quickly filling values into blank cells in an Excel spreadsheetApril 25, 2023
Learn the Top 10 Benefits of Microsoft Excel and Why You Should Consider Learning ItApril 25, 2023Learn the Top 10 Benefits of Microsoft Excel and Why You Should Consider Learning ItNext

Leave a Reply Cancel reply

You must be logged in to post a comment.

Buy Windows 11 Professional MS Products CD Key
Buy Office 2021 Professional Plus Key Global For 5 PC
Top rated products
  • Windows Server 2022 Datacenter Key Global Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $7.00
  • Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year Kaspersky Small Office Security 10 PCs + 10 Mobiles + 1 Server 1 Year
    Rated 5.00 out of 5
    $164.50
  • Avast Ultimate Suite 2021 1 Year 10 Devices Global Avast Ultimate Suite 2021 1 Year 10 Devices Global
    Rated 5.00 out of 5
    $54.00
  • Windows 11 Pro Key Global Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $6.00
  • Avast Premium Security 2021 10 Devices 2 Years Global Avast Premium Security 2021 10 Devices 2 Years Global
    Rated 5.00 out of 5
    $41.00
Products
  • Microsoft Visio Standard 2021 Key 1PC Microsoft Visio Standard 2021 Key 1PC $13.00
  • Windows 10 Enterprise Key Global Windows 10 Enterprise Key Global
    Rated 4.82 out of 5
    $9.00
  • Kaspersky Total Security 2021 1 year 1 device key Global Kaspersky Total Security 2021 1 year 1 device key Global
    Rated 5.00 out of 5
    $27.00
  • Microsoft Visual Studio 2022 Professional Key Microsoft Visual Studio 2022 Professional Key $19.00
  • Kaspersky Small Office Security 15 PCs + 15 Mobiles + 2 Servers 1 Year Kaspersky Small Office Security 15 PCs + 15 Mobiles + 2 Servers 1 Year
    Rated 5.00 out of 5
    $201.00
  • Kaspersky Plus - 1 Device 1 Year - GLOBAL Kaspersky Plus - 1 Device 1 Year - GLOBAL $23.00
  • Microsoft Project 2019 Professional Key Global Microsoft Project 2019 Professional - 5 PC
    Rated 4.97 out of 5
    $12.00
  • Avast SecureLine VPN 2021 2 Years 5 Devices Global Avast SecureLine VPN 2021 2 Years 5 Devices Global
    Rated 5.00 out of 5
    $47.00
  • Trend Micro Internet Security 3 Devices 1 Year Key GLOBAL Trend Micro Internet Security 3 Devices 1 Year Key GLOBAL
    Rated 5.00 out of 5
    $17.00
  • Office 2024 Home And Business For Mac/Pc Bind Office 2024 Home And Business For Mac/Pc Bind $99.00
Product categories
  • Anti Virus
  • Microsoft Office
  • Microsoft Project
  • Microsoft Visio
  • Microsoft Visual
  • Microsoft Windows
  • Other Software
  • Uncategorized

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.