#Value! Error

[jason_1]

Hey

I have the following formula -

=COUNTIF('\\Europemuk317\HSD\HNG REPORTING & TREND TEAM\[Frontline HNG fad list.xls]Horizon Online Branches'!$C:$C,"30/07/2010")

Which produces a #VALUE!, the cell above has the following formula

=COUNTIF('\\Europemuk317\HSD\HNG REPORTING & TREND TEAM\[Frontline HNG fad list.xls]Horizon Online Branches'!$C:$C,"29/07/2010")

This work correctly as does every cell above for the last 2 months. The file it links to contains the data needed and hasn't been changed in any way. The same error is also happening on the cell below for the 31/07/2010.

Any Ideas?

 

[Richard Schollar]

You are performing a count on a text string rather than a date value - try writing your COUNTIF like this:

=COUNTIF('\\Europemuk317\HSD\HNG REPORTING & TREND TEAM\[Frontline HNG fad list.xls]Horizon Online Branches'!$C:$C,DATEVALUE("30/07/2010"))

Note that COUNTIF only works with open workbooks - so the target workbook (ie Frontline HNG fad list.xls) must be open when this formula recalculates.

 

[jason_1]

Hi Richard,

I have just changed the formula to the one above, and it still produces a #value! error.

It is also producing it on another cell with the following formula: =COUNTIF('\\Europemuk317\HSD\HNG REPORTING & TREND TEAM\Daily\HNG Call Analysis\JULY\[July HNG calls.xls]3007'!$M:$M,"WK????????")

It's only happening on the 31st and 30th, all cells about work fine with no issues

 

[Richard Schollar]

Is the target file open? As i said before, it must be open for the formula to work.

 

[jason_1]

Hi richard,

Both files were open at the time. I sent you a like via PM to the file download, i hope you don't mind.

Many Thanks

 

[Richard Schollar]

Hi Jason

I have replied to your PM. I couldn't reproduce the error (it worked as expected for me). I did note that you could reference the B column cell containing your date rather than writing it out in the Countif again:

=COUNTIF(Range,B53)