Countif

[Guest]

I have a very large spreadsheet where I am using Countif to count the data in the first column. It works perfectly for the first 10,750 rows and then returns the wrong count for the remaing rows. Example: 12 when the correct count should be 9. I have 575 rows remaining. If I cut and paste, say 20 rows into another spreadsheet the count is correct on the new spreadsheet. If I paste all 575 rows into a new spreadsheet the count is wrong. The spreadsheet is set up as follows

Column A Column
NLM =COUNTIF(A:A,A1) result is
NLM =COUNTIF(A:A,A2) result is
NLM =COUNTIF(A:A,A3) result is
WLK =COUNTIF(A:A,A4) result is
WLK =COUNTIF(A:A,A5) result is

The count is used for a summary on another worksheet in the same workbook

Does any of this make sense

 

[Frank Kabel]

Hi
never heard of this. If you like email me your file and I'll have a
look at it
email: frank[dot]kabel[at]freenet[dot]de

 

[Peo Sjoblom]

Use the advanced filter to extract a unique list from A
(data>filter>advanced filter, copy to another location,
unique records only), then use that list to refer to your count,

assume that you copy the unique list into D21, use

=COUNTIF(A:A,D2)

copy down along the filtered list.
Why do you count the same value over and over?

--

Regards,

Peo Sjoblom

I have a very large spreadsheet where I am using Countif to count the data

in the first column. It works perfectly for the first 10,750 rows and then
returns the wrong count for the remaing rows. Example: 12 when the correct
count should be 9. I have 575 rows remaining. If I cut and paste, say 20
rows into another spreadsheet the count is correct on the new spreadsheet.
If I paste all 575 rows into a new spreadsheet the count is wrong. The
spreadsheet is set up as follows.

 

[Peo Sjoblom]

Should of course be D2, not D21

 

[Frank Kabel]

Hi
yes do just that

 

[Harlan Grove]

I have a very large spreadsheet where I am using Countif to count the data in
the first column. It works perfectly for the first 10,750 rows and then
returns the wrong count for the remaing rows. Example: 12 when the correct
count should be 9. I have 575 rows remaining. If I cut and paste, say 20 rows
into another spreadsheet the count is correct on the new spreadsheet. If I
paste all 575 rows into a new spreadsheet the count is wrong. The spreadsheet
is set up as follows.

Column A Column B
NLM =COUNTIF(A:A,A1) result is 3
NLM =COUNTIF(A:A,A2) result is 3
NLM =COUNTIF(A:A,A3) result is 3
WLK =COUNTIF(A:A,A4) result is 2
WLK =COUNTIF(A:A,A5) result is 2

The count is used for a summary on another worksheet in the same workbook.

If your counts were understated, I'd suspect variable trailing space characters.
However, your count is overstated, so do any of your column A entries contain
either ? or * characters?

 

[Frank Kabel]

Hi
as a follow-up:
Nothing was wrong with the formulas. Just some additional matching
values at the end of the range (which seemed to be sorted but wasn't)