CountA issues

[lljones]

Using COUNTA to exclude blanks – having issues

I have a spreadsheet that has 101 rows of data (First row contains column headings)

Column B is a text field. There are four blank cells, so the correct count is 96.

=COUNTA(B2:B101) returns 96
=COUNTA(B:B) returns 98
=COUNTA(RGIndex!Authors) returns 98 <--named range

Column C is a numeric field. Same four blank cells so correct count is 96.

=COUNTA(C2:C101) returns 96
=COUNTA(C:C) returns 97
=COUNTA(RGIndex!BookCt) returns 97 <--named range

How do I get the correct results without stating a specific range (B2:B101, etc.)?

Thanks in advance.

 

[Gerald Higgins]

Do you have some entries in column B, below B101, that shouldn't be there ?

If yes, consider deleting them or moving them.

If you need them to stay where they are, and you know there will only ever be 2 of them, consider something like
=counta(b:b)-2

 

[lljones]

No, nothing below row 101.

Sorry, I had a typo in my first post. Should look like this:

=COUNTA(B2:B101) returns 96
=COUNTA(B:B) returns 97
=COUNTA(RGIndex!Authors) returns 97 <--named range

Column C is a numeric field. Same four blank cells so correct count is 96.

=COUNTA(C2:C101) returns 96
=COUNTA(C:C) returns 97
=COUNTA(RGIndex!BookCt) returns 97 <--named range

I'm assuming the issue has to do with Row 1 - column headings - ????

 

[Gerald Higgins]

Ah, yes, I should have thought of that, probably column headings are causing this, assuming you actually have a column heading in Cols B and C.

Again, if you need them, and you know you will only ever have 1 row of column headings, then consider using
=counta(b:b)-1

 

[Muhammad_Usman]

Rename Your range without Heading, It will sort out your problem.

Name Range With Heading Result =COUNTA(km)
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Total[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Total Name Range Without Heading Result [/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]