Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Issues
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Issues
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Issues
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Issues
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Issues
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Issues
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Issues
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date formula issues?!?! | Excel Discussion (Misc queries) | |||
Formula issues | Excel Worksheet Functions | |||
I'm having formula issues | Excel Discussion (Misc queries) | |||
formula issues | Excel Worksheet Functions | |||
Issues with formula not duplicating | Excel Worksheet Functions |