Solved

# Changing Countif to Sumif formula so works if external workbook is closed

Posted on 2014-03-11

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