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
