Workbook 2 (DailyMetrics.xlsm) contains the statistics based on data in Workbook 1 (CAP_29MAY.xlsm) using the "Countif" formula. But if Workbook 1 is closed, then Workbook 2 shows #VALUES instead of numbers. In researching this, it appears that Countif doesn't work when linking to closed Workbooks, but Sumif does. I am trying to convert my Countif formula to a Sumif formula, but the Sumif calculations vary from the Countif ones.

Countif formula: (result is "12")

=COUNTIFS(CAP_29MAY2014.xlsm!LOE_Range,"Major",CAP_29MAY2014.xlsm!MasterCompUpdateRange,"<>",CAP_29MAY2014.xlsm!ADC_AMD_Range,"ADC*")

Sumif formula: (result is "0")

=SUM(IF(CAP_29MAY2014.xlsm!LOE_Range="Major",IF(CAP_29MAY2014.xlsm!MasterCompUpdateRange="<>",IF(CAP_29MAY2014.xlsm!ADC_AMD_Range="ADC*",0)*1,0)))

I need to know if I'm correct in saying that the Sumif formula will work if Workbook 1 is closed. If not, then is there an alternative formula that can be implemented to work under these conditions? And if so, then how do I translate my Countif formula to a Sumif formula correctly so I get the same results?

Thanks,

Andrea

None of the COUNTIF/SUMIF/AVERAGEIF "family" of formulas works with closed workbooks - that's because those functions expect ranges (not arrays) as the arguments and when the ranges are retrieved from closed workbooks they become arrays, which doesn't work.

I think you can use SUMPRODUCT, as Rob says, but one of your criteria uses a wildcard - that won't work in SUMPRODUCT (or within a SUM9IF array formula) so you need

LEFTfunction - try like this:=SUMPRODUCT((CAP_29MAY2014

regards, barry