Formula Issues

OEMJ · External Since: Nov 02, 2009 Posts: 3

Hi, I am trying to create a formula which counts the number of cells in a
column range which do not equal "n/a". I am at a total loss...any suggestions?

Max · External Since: Mar 17, 2004 Posts: 1751

One way
Assume source range to be checked is A2:A10
In B2: =COUNTA(A2:A10)-SUMPRODUCT(--ISNA(A2:A10))
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"OEMJ" wrote:
> Hi, I am trying to create a formula which counts the number of cells in a
> column range which do not equal "n/a". I am at a total loss...any suggestions?

Jacob Skaria · External Since: Mar 04, 2009 Posts: 441

If you mean count the ** number of cells ** then try

'if you mean the error #N/A try
=COUNTIF(A1:A10,"<>#N/A")

'if you mean text n/a
=COUNTIF(A1:A10,"<>N/A")

If this post helps click Yes
---------------
Jacob Skaria

"OEMJ" wrote:

> Hi, I am trying to create a formula which counts the number of cells in a
> column range which do not equal "n/a". I am at a total loss...any suggestions?

OEMJ · External Since: Nov 02, 2009 Posts: 3

Thanks Max but this returns the wrong amount. I am aiming for a figure of 47
but this is obviously going to change when i amend the data. This gave me 67.
I have tried another formula:

=COUNTIF(N4:N107,"<>n/a")

but this includes blank cells which i do not want included and gives me a
sum of 84. Any ideas?

"Max" wrote:

> One way
> Assume source range to be checked is A2:A10
> In B2: =COUNTA(A2:A10)-SUMPRODUCT(--ISNA(A2:A10))
> Any good? hit the YES below
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:27,000 Files:200 Subscribers:70
> xdemechanik
> ---
> "OEMJ" wrote:
> > Hi, I am trying to create a formula which counts the number of cells in a
> > column range which do not equal "n/a". I am at a total loss...any suggestions?

OEMJ · External Since: Nov 02, 2009 Posts: 3

I think I have discovered the winning formula...

=COUNTA(N4:N107)-COUNTIF(N4:N107,"n/a")

This returns the correct result but does it make sense?

"OEMJ" wrote:

> Hi, I am trying to create a formula which counts the number of cells in a
> column range which do not equal "n/a". I am at a total loss...any suggestions?

Max · External Since: Mar 17, 2004 Posts: 1751

The earlier presumes you meant the excel error: #N/A
COUNTA gives you the count of all filled cells in the source range, whether
these contain text, nums or #N/As, ie it excludes blank cells. The sumproduct
counts the number of cells with #N/As. The "counta - sumproduct" earlier
hence returns the desired count (provided you meant: #N/A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"OEMJ" wrote:
> Thanks Max but this returns the wrong amount. I am aiming for a figure of 47
> but this is obviously going to change when i amend the data. This gave me 67.
> I have tried another formula:
>
> =COUNTIF(N4:N107,"<>n/a")
>
> but this includes blank cells which i do not want included and gives me a
> sum of 84. Any ideas?

Max · External Since: Mar 17, 2004 Posts: 1751

It does, if you actually meant the text: "n/a" instead of the excel error: #N/A
Please refer to my further reply to you in the other branch of this thread
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"OEMJ" wrote:
> I think I have discovered the winning formula...
>
> =COUNTA(N4:N107)-COUNTIF(N4:N107,"n/a")
>
> This returns the correct result but does it make sense?