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?
Below a summary that uses SUMIF to consolidate all the blue cells. Works fine when all are opened.
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
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.
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.
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.