We help IT Professionals succeed at work.

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

beauty11
beauty11 asked
on
range-A5:A13
Comment
Watch Question

Director, SD-WAN Solutions
CERTIFIED EXPERT
Commented:
=count(a5:a13)

Also look at countif
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Commented:
=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.
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

Commented:
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.

CERTIFIED EXPERT

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

Commented:
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))))}

Explore More ContentExplore courses, solutions, and other research materials related to this topic.