SUMIF Results - Excel 2010 vs Excel 2013

I recently upgraded to Excel 2013 and am noticing differences in the values that my SUMIF functions report.

When I had Excel 2010, sometimes my SUMIF formulas would error out (get a #VALUE I believe) when linking to another workbook if said other workbook was not open. It was easy to fix by opening that other workbook and the error would clear out.

Now in Excel 2013 I do not get an error - the result that is returned is a zero if the linked workbook is not open. This is a problem because the user may assume the zero is the correct value that is being pulled in from the other workbook when it is not. Is there a way to have Excel keep that last value the SUMIF returned when the workbook was previously saved or will I have to instruct the user to make sure all linked workbooks are open in order to generate accurate results? I often use the linked workbooks as references to point where the data came from and these linked workbooks are most often static - thus not having to open all the linked workbooks would save the user time.

You could just use SUMPRODUCT instead of COUNTIF. SUMPRODUCT does not require workbooks be open to work correctly.

=SUMPRODUCT((criteria range=criteria) * (sum range)) normally SUMs the PRODUCTs of two arrays or matrices, but it can be tricked into only adding up the count of the cells of the criteria.

So, COUNTIF(A1:A5000,>5) becomes =SUMPRODUCT((A1:A500>5)*1) and does the same thing.

If you also need it to exclude blanks, just add that as the multiplier instead of 1.

=SUMPRODUCT((A1:A500>5)*(A1:A500<>"")) This is the COUNT of cells from A1:A500 that are greater than 5 and not blank.