• Microsoft Office
  • Microsoft Windows
  • Other Software
  • 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
  • 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
  • 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
  • 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.

4
58 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

Your email address will not be published. Required fields are marked *

Recent Posts
  • Retrieve Data Based on Data Validation
    September 7, 2023
    Excel
  • How to Convert Multiple Rows to Columns and Rows in Excel?
    September 6, 2023
    Excel
  • VLOOKUP for Automatic Discount in Data Validation
    September 6, 2023
    Excel
  • How to Convert Text Dates to Dates in Excel?
    September 5, 2023
    Excel
Categories
  • Blog 80
  • Excel 754
  • Powerpoint 36
  • Software tricks/tips 128
  • Uncategorized 8
  • Word 109
Tags
the ISERROR
Top rated products
  • Avast Premium Security 2021 Avast Premium Security 2021 1 Device 1 Year Global
    Rated 5.00 out of 5
    $11.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
  • Windows 11 Pro Key Global
    Rated 5.00 out of 5
    $11.00
  • AVG Internet Security 2021 10 Devices 2 Years Global AVG Internet Security 2021 10 Devices 2 Years Global
    Rated 5.00 out of 5
    $42.20
  • Trend Micro Maximum Security 3 Devices 1 Year key Global Trend Micro Maximum Security 3 Devices 1 Year key Global
    Rated 5.00 out of 5
    $15.00
Products
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.83 out of 5
    $49.00
  • Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $15.00
  • Windows Server 2022 Standard Key Global
    Rated 4.47 out of 5
    $15.00
  • Windows Server 2022 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00

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.