• Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
  • Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
Quick Support
Book Demo

How to avoid the VALUE error when using the SUMIF function between workbooks

By admin on June 23, 2017April 19, 2019

When using the SUMIF function between workbooks, you may get a VALUE error if the source workbook is not open. This behavior occurs when the formula that contains the SUMIF, COUNTIF, or COUNTBLANK function refers to cells in a closed workbook. To work around this, use a combination of the SUM and IF functions together in an array formula.
An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas act on two or more sets of values known as array arguments.
In this example, we calculate the total sales for the seafood product category. The result will be placed in the report workbook and the source data is in the data workbook.
You are welcome to download workbook one here (Data) and two here (Report) to practice this tip.
Applies To: Microsoft® Excel® for Windows 2010, 2013, 2016.
1. Open the workbook that contains the source data (Data workbook).
2. Open the workbook that will contain the formula (Report workbook).
3. Select cell C5 in the report workbook.
4. Using the FX button on the Formula Bar, locate the Sum Function.
5. To nest in the IF Function, from the Formula Bar, in the Name Box,  from the drop-down arrow, select IF.
6. If the IF function does not appear, select More Functions and locate the IF Function.
7. Enter in the arguments as below:
Logical_test : Data.xlsx!$A$2:$A$30=”Seafood”.
Value_If _true: SUM(Data.xlsx!$D$2:$D$30).
Value_if_false: 0.

8. To complete the array formula Press Ctrl + Shift + Enter.
9. Select Yes if asked to correct the formula. The name ranges could have also been defined for CategoryNames and ProductSales.
By using this method, we can avoid encountering the value error when the data(source) workbook is not open. This will eliminate the time spent on troubleshooting and correcting errors.
The post How to avoid the VALUE error when using the SUMIF function between workbooks appeared first on Sage Intelligence.

Powered by WPeMatico

Post navigation

How to delete data but keep your formulas on a worksheet in Excel
How to reverse the sign of an account in your Sage Intelligence Financial Report Designer

Recent Posts

  • Why Working In Excel Works
  • Alchemex Welcomes Velixo 6 For MYOB Advanced
  • Welcome to Velixo Reports Version 4.2
  • Part 3 of 3: Learn how to create a Management Pack in Sage 50cloud Intelligence
  • Part 3 of 3: Create and customize reports easily with the Financial Report Designer

Search

Contact Us

Phone: +61 2 9890 2339
Email: sales@alchemex.com.au
Address: PO Box 212, Deaken West, Canberra, Australia, 2600

Products

  • Intelligence Reporting
  • Velixo Reports

Services

  • Consulting
  • Training
  • Development

About

  • About Us
  • Partners
  • Blog

Support

  • New Ticket
  • Community (Coming Soon)
  • Knowledgebase

Phone: +61 2 9890 2339
Sales: sales@alchemex.com.au
Support: support@alchemex.com.au

Facebook-f Linkedin

© All rights reserved Alchemex Pty Ltd 2020

Made with ❤ by Unfazed Ltd