countif formula seems to be working wrong sometimes

Asked by dale francis

I have a simple formula using COUNTIF($B7:$Q7,T$1) in cell T6. The T6cell displays 4. Cells G6, K6, L6, M6 & N6 all have N as does cell T1. The N in cell K6 is the one not being counted. I can delete that cell contents and the count remains 4. I can enter any character including an N and the count remains 4. The cell does not seem to be formatted any way differently than other cells.

There are versions of this formula in many cells and most work properly. As best I can tell, only characters in col K & I are not treated properly, and then only part of the time.
Any ideas on what is wrong.

I am running Ubuntu 10.04 on an ASUS Eee 900 using Open Office 3.2 with the spread sheet in Excel mode. I have tried saving the spreadsheet in native Open Office mode with the same result.

Thanks for any help. I assume this problem is in the particular spreadsheet, so if I can figure out how to attach it I will.

Thanks, JenCliff

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Tony Pursell
Solved:
Last query:
Last reply:
Revision history for this message
Tony Pursell (ajpursell) said :
#1

Hi JenCliff

I have tried COUNTIF myself without problems.

Can I suggest that you copy all the tested area ($B7:$Q7) to a new document then set up T1 and the formula in T6 and see what you get.

If you use Paste Special, you can control what is pasted under Selection, but try a normal Paste first to see if that cures it.

You can also see if there is any difference if you paste to an ods or xls spreadsheet.

Tony

Revision history for this message
dale francis (p-dale-p) said :
#2

Tony (and others)

I did copy then paste B2:T70 to the following: 1)sheet 2 same spreadsheet, 2)sheet 1 new excel spreadsheet, 3)sheet 1 new OO spreadsheet; then in each case inserted N in cell T1
Also copy then paste special B2:T70 to the following: 1)sheet 2 same spreadsheet, 2)sheet 1 new excel spreadsheet, 3)sheet 1 new OO spreadsheet; then in each case inserted N in cell T1

Same result each time. Still get a count of 4 when it should be 5

How do I attach the spreadsheet to the question, which might help get a result.

JenCliff

Revision history for this message
Tony Pursell (ajpursell) said :
#3

Hi JenCliff

There is no facility to attach files here. See

https://bugs.launchpad.net/launchpad/+bug/77123

If you want, you can email the file to me. Click on my name to see my details, including email address.

Tony

Revision history for this message
dale francis (p-dale-p) said :
#4

Tony,

I have sent the spreadsheet to your email.

JenCliff

Revision history for this message
Best Tony Pursell (ajpursell) said :
#5

Hi JenCliff

Thanks for the file. I have had a look at it. There is a difference between columns J and K and the others, but I cannot, for the moment, tell you what it is. What I have found, is that if you place 'n' in columns J and K, it is converted to 'N', and this does not happen in the other columns.

The only cure I can give you is to delete columns J and K (with Edit > Delete Cells), insert two new, blank columns and re-enter the data.

I may be able to come up with more information or a better cure, but I am quite busy with other activities over the Xmas and New Year period,

Tony

Revision history for this message
Tony Pursell (ajpursell) said :
#6

Hi JenCliff

I have found the answer!

Some of the cells have a space after the character. If you delete this trailing space, then the COUNTIF() will work OK.

They are all in columns J and K. You can use Edit > Find & Replace to find them by putting

^.([:space:])$

In the 'Find' box and checking 'Regular expressions' under 'More Options'

Tony

PS if this answers your question now, please mark it as Solved

Revision history for this message
dale francis (p-dale-p) said :
#7

Tony,

That worked. Your comment about the lower case 'n' caused me to look a little further. The situation is created by the auto-correct feature wanting to capitalize the first word of a sentence.

I am not sure why only some columns were affected.

Thanks for the help

JenCliff

Revision history for this message
dale francis (p-dale-p) said :
#8

Thanks Tony Pursell, that solved my question.