Values show error when using COUNTIF function on closed workbook.

[Jermine]

Hi there, I need help with thee below issue.

Can someone pls advice on how to amend the below countif formula to array formula?
=COUNTIFS(OFFSET('[2. Duty Roster Email - Feb 2021.xlsx]Feb'!$G$18:$G$45,0,(ROW(CB1)-1)*5),"*-*")

with the said formula, the correct value is showing when the reference workbook is open, but the result will show an error when it is closed.
And after googling online, I found that countif formula does not work when the reference workbook is closed, but an array formula can solve this issue.

However, I failed to get the right one after many tries.

 

[Yongle]

Welcome to MrExcel

Sorry I cannot provide you with a solution, but OFFSET does not work with a closed workbook either

 

[jasonb75]

For a closed workbook you need to use SUMPRODUCT. Best guess from trying to decode your current formula without any information as to what it should be doing.

=SUMPRODUCT(--ISNUMBER(SEARCH("-",INDEX('[2. Duty Roster Email - Feb 2021.xlsx]Feb'!$18:$45,,(ROW(CB1)-1)*5)+7))))

 

[Jermine]

For a closed workbook you need to use SUMPRODUCT. Best guess from trying to decode your current formula without any information as to what it should be doing.

=SUMPRODUCT(--ISNUMBER(SEARCH("-",INDEX('[2. Duty Roster Email - Feb 2021.xlsx]Feb'!$18:$45,,(ROW(CB1)-1)*5)+7))))

Hi Jason, thank you so much for responding.

The purpose of the current formula is to count cell in a range that contains dash "*-*", and in this case, is how many cell in a range that contains dash "*-*" for each date.
while OFFSET *5 is because the width for each date is set to 5.

Not sure if that's a good formula to get the value, but that's what I manage to figure out for now.

Below is the screenshot, and I hope it clarifies.

 

[jasonb75]

What happened with the formula that I suggested?

 

[Jermine]

It returns a value of 0, whereby the answer should be 16.

this is the reference excel file, would you mind having a look.

Duty Roster - Feb 2021.xlsx

drive.google.com

 

[Jermine]

What happened with the formula that I suggested?

It returns a value of 0, whereby the answer should be 16.

this is the reference excel file, would you mind having a look.

Duty Roster - Feb 2021.xlsx

drive.google.com

 

[Fluff]

Looks as though Jason has a slight typo (one too many closing brackets) try

=SUMPRODUCT(--ISNUMBER(SEARCH("-",INDEX('[2. Duty Roster Email - Feb 2021.xlsx]Feb'!$18:$45,,(ROW(CB1)-1)*5+7))))

 

[Jermine]

Looks as though Jason has a slight typo (one too many closing brackets) try

=SUMPRODUCT(--ISNUMBER(SEARCH("-",INDEX('[2. Duty Roster Email - Feb 2021.xlsx]Feb'!$18:$45,,(ROW(CB1)-1)*5+7))))

That formula works wonderfully now, Thank you Fluff.

 

[Fluff]

Glad we could help & thanks for the feedback.