- 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.