SUMIFS returns VALUE when linked workbooks are closed

You’ve built your detailed budget and cross linked all the workbooks. Now you want to consolidate the results and see what it says. You use SUMIFS to summarise the data. Problem is that if ALL the workbooks aren’t open, instead of seeing the numbers you see #Value! errors.  Can you address the situation where SUMIFS returns value when linked workbooks are closed?

Estimated reading time: 3 minutes

YouTube SUMIFS returns VALUE

Watch, like, subscribe

SUMIFS when range workbook closed

Below a summary that uses SUMIF to consolidate all the blue cells. Works fine when all are opened.

SUMIFS returns value when linked workbooks are closed

Problem is, if you open the file with the SUMIFS in it on its own, you get #VALUE! errors where the numbers should be as shown below

SUMIFS returns value when linked workbooks are closed

Unfortunately this is the way SUMIF and SUMIFS work. If you don’t have too many links, then the solution is to open all the workbooks at the same time.

However, if there are too many links and you want to be able to see the numbers, then a suggestion is to rather use the SUMPRODUCT function.

Turn SUMPRODUCT into a working SUMIFS

In order to make it easier to see, we have moved the source data onto the same sheet, but in real life you would just link to the other workbook.

We create a SUMPRODUCT function. In the first array we highlight the range we would have specified as the criteria range in the SUMIFS and type = B14 as shown below. In SUMPRODUCT this would generate a TRUE and FALSE but we need 1’s and 0’s so we put this in brackets and type a double negative in front. This changes the TRUE’s and FALSE’s into 1’s and 0’s. In the second array we highlight the range we want to ‘sum if it is true’, in this case column F.

SUMIFS returns value when linked workbooks are closed

SUMPRODUCT will multiply the matching sale with a 1 or a 0 depending on whether it matches the criteria.

The benefit of using SUMPRODUCT instead of SUMIFS is that when linked workbooks are closed, you still see the numbers.

SUMIFS returns value when linked workbooks are closed

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

SUMIFS Value Error

Tracing errors like #VALUE! or #DIV/0! or #NUM! in Excel