'COUNTIF' Function is not preventing '#N/A'

**carlosbourn** · 06-05-2008, 05:57 AM

I am using the following COUNTIF formula which I am sure is supposed to prevent '#N/A' from appearing (According to http://www.ozgrid.com/Excel/stop-na-vlookup.htm)

Please Login or Register  to view this content.

However, #N/A is still appearing in the worksheet when no data has been input.

As this is something we are going to be presenting to a client, I am keen to ensure it doesn't contain #N/A all over it!

Thanks in anticipation

**arthurbr** · 06-05-2008, 06:18 AM

Originally Posted by carlosbourn

I am using the following COUNTIF formula which I am sure is supposed to prevent '#N/A' from appearing (According to http://www.ozgrid.com/Excel/stop-na-vlookup.htm)

Please Login or Register  to view this content.

However, #N/A is still appearing in the worksheet when no data has been input.

As this is something we are going to be presenting to a client, I am keen to ensure it doesn't contain #N/A all over it!

Thanks in anticipation

I don't really understand the first part
B10 seems to be the range you test, is PORT the test criteria?

**carlosbourn** · 06-05-2008, 06:27 AM

Correct - B10 is the range & PORT is the criteria

(arthurbr - this is based almost entirely on a forumula you helped me with a month or so ago!)

**arthurbr** · 06-05-2008, 06:31 AM

Originally Posted by carlosbourn

Correct - B10 is the range & PORT is the criteria

(arthurbr - this is based almost entirely on a forumula you helped me with a month or so ago!)

Thx for reminding me
But I see thet PORT is also used as 2nd in your MATCH function where it should use an array??

**carlosbourn** · 06-05-2008, 06:48 AM

Hmm, I see what you mean but the formula seems to work absolutely fine and I think the formula you used in my sample last time was also set up like this - see below:

Please Login or Register  to view this content.

**arthurbr** · 06-05-2008, 07:50 AM

Could you post your file?

**carlosbourn** · 06-05-2008, 08:46 AM

Here you go

**EdMac** · 06-05-2008, 09:22 AM

Hi,

This in G4 and copied down will do what you want

Please Login or Register  to view this content.

BUT

Why can't you use a much simpler construct like this

Please Login or Register  to view this content.

**carlosbourn** · 06-05-2008, 09:45 AM

EdMac - thanks.

Tried the 1st formula but unfortuntely does not work in the finished workbook (the workbook provided was only a small sample)

It leaves the cell blank even when entering data in A, B, & C4. I thought that it would only leave blank if those cells are left blank?!?

With regards to the 2nd formula this does not work as the rate depends on the date (ETA) as well as the port / location.

Thanks

**EdMac** · 06-05-2008, 10:11 AM

Hi,

First formula works for me - Can't see why it should behave as you describe.

Have you tried using the Formula auditing tools to see where it fails select the cell - Tools > Formula auditing > evaluate

I think that you could leave one expression out to make it

Please Login or Register  to view this content.

You could use lookups to allow for different dates, but Index and match work just as well.

regards

**carlosbourn** · 06-05-2008, 10:28 AM

Thanks Ed

This is what the formula looks like on the 'mothership' version(!):

Please Login or Register  to view this content.

I have replaced it with the below:

Please Login or Register  to view this content.

The cell is now blank, which is great; exactly what I wanted, however when I insert data into A, B & C10 (in this instance) I get #N/A again!

I've grown to really hate #N/A now!