**one here (Data)**and

**two here (Report)**to practice this tip.

*Applies To: Microsoft*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:

^{ }Excel^{ }for Windows 2010, 2013, 2016.- Logical_test : Data.xlsx!$A$23:$A$30="Seafood".
- Value_If _true: SUM(Data.xlsx!$D$23:$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.__