How do I enter a function that counts the number of entries in a specified range

Hello,

as aarontomosky suggests, Count() will work, but for for numeric values only.

=CountA(A5:A13) will count only text values

=COUNTIF(A:A,"<>") will count any data type.

cheers, teylyn

=Count() will work if the fields are numeric (countable).

In situations where the fields are not, I add or use column to the right and and IF statement and then sum or count that column range.

For example.
the data in column A:

Row:
5    "text"
6
7    "more text"
8    124
9
10

In column B I copy the following formula down the range.

=if(a5<>"",1,0)
...
...
=if(a10<>"",1,0)

Basically, if(the field is not blank, value is 1, otherwise field is blank and value 0)

Then, I sum up the values in column Bs range (in this case 3 of the 6 fields have values).

If I don't want the evaluating column to be visible, then I either change the text color to match the background or I hide the column.

Hopefully that makes sense.  If not, please ask.

Hi, beauty11.

Are you looking for the number of cells, the number of non-blank cells, the number of unique values or the number of non-blank unique values?

If you're looking for unique values, will the values all be numeric and is it sorted?

Thanks,
Brian.

beauty11,

The number of...
(A) cells
=ROWS(A5:A13)
(B) non-blank cells
=COUNTIF(A5:A13,"<>")
(C) unique values, including blanks
{=SUM(IFERROR(1/(COUNTIF(A5:A13,A5:A13)),1))-COUNTIF(A5:A13,"")+IF(COUNTIF(A5:A13,"")>0,1,0)}
(D) unique non-blank values.
{=SUM(IFERROR(1/(COUNTIF(A5:A13,A5:A13)),1))-COUNTIF(A5:A13,"")}

Please note that (C) and (D)...
(1) ... have to be array entered, i.e. use Ctrl+Shift+Enter rather than Enter.
(2) ... assume you're using Excel 2007 or 2010.
(3) ... can both be considerably simpler (and not require Excel 2007 or 2010) if there can never be blanks in the range.

Regards,
Brian.

Oh, when you array-enter a formula, you don't type the { and } characters as the Ctrl+Shift+Enter adds those automatically.

And the versions of C and D for all versions of Excel...

(C) unique values, including blanks
{=SUM(1/(IF(COUNTIF(A5:A13,A5:A13)=0,10^99,COUNTIF(A5:A13,A5:A13))))+IF(COUNTIF(A5:A13,"")>0,1,0)}

(D) The number of unique non-blank values
{=SUM(1/(IF(COUNTIF(A5:A13,A5:A13)=0,10^99,COUNTIF(A5:A13,A5:A13))))}