Excel formula referenced to another closed workbook does not update data

Question

Below is my formula (in workbook A) referenced to another workbook B where the data is present and frequently updated.

=COUNTIFS('Z:\Reports\Data\[Athletics - LIVE.xlsx]Sheet 1'!C:C,"Media",'Z:\Reports\Data\[Athletics - LIVE.xlsx]Sheet 1'!A:A,">="&B1,'Z:\Reports\Data\[Athletics - LIVE.xlsx]Sheet 1'!A:A,"<="&B2)

B1 and B2 (format: date) are the cells present in workbook A itself (where the formula is present). The formula looks for strings with two criteria in workbook B and give us the counts (requirement). The result I get is #VALUE!unless the closed workbook B is opened.

Is there a problem with indexing? Why the formula does not update when the workbook B is updated and closed?

Answer 1

COUNTIF(S)/SUMIF(S) don't work with closed workbooks. You'll need to use something like SUMPRODUCT instead - but don't use entire column references:

=SUMPRODUCT(('Z:\Reports\Data\[Athletics - LIVE.xlsx]Sheet 1'!C1:C1000="Media")*('Z:\Reports\Data\[Athletics - LIVE.xlsx]Sheet 1'!A1:A1000>=B1)*('Z:\Reports\Data\[Athletics - LIVE.xlsx]Sheet 1'!A1:A1000<=B2))

for example.