Countif

  • Thread starter Thread starter Guest
  • Start date Start date
G

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


Matt said:
I have a very large spreadsheet where I am using Countif to count the data
Click to expand...
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.
 
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.
Click to expand...

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?
 
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)
 
Back
Top