Help working IF(COUNTIF round a bug

[Sam_Daniels]

Hi all.

I have tried using formula =IF(COUNTIF(WorkingsOut!I4:I40,'Drop Down Data'!E3),'Drop Down Data'!H3,IF(COUNTIF(WorkingsOut!I4:I40,'Drop Down Data'!E4),'Drop Down Data'!H3,'Drop Down Data'!H2)) which links to a different workbook (Via a macro). However, I am led to believe there is a bug in excel that makes the link come up as #VALUE !

Is there a formula I can use instead of this to work around it?

Many thanks

 

[Truiz]

Hi,

you need to use the proper reference =IF(COUNTIF('[File Name.xslx]SheetName'!A1:A100 ...

 

[Jonmo1]

However, I am led to believe there is a bug

By who?
Do you have a link to an article?

The formula you posted works as expected, tested in my book.

 

[barry houdini]

COUNTIF won't work if it references a closed workbook. In that scenario you can switch to SUMPRODUCT, something like

=IF(SUMPRODUCT((WorkingsOut!I4:I40='Drop Down Data'!E3)+0),'Drop Down Data'!H3,IF(SUMPRODUCT((WorkingsOut!I4:I40='Drop Down Data'!E4)+0),'Drop Down Data'!H3,'Drop Down Data'!H2))

 

[Jonmo1]

Just to actually answer the question,
Here's an alternate formula

=IF(OR(ISNUMBER(MATCH('Drop Down Data'!E3,WorkingsOut!I4:I40,0)),ISNUMBER(MATCH('Drop Down Data'!E4,WorkingsOut!I4:I40,0))),'Drop Down Data'!H3,'Drop Down Data'!H2)