COUNTIF not counting properly

[Christine]

One of my users has a spreadsheet in which column A has a variety of string
entries. I am trying to count the number of times the string, WAGE, occurs.
I'm using the function: =COUNTIF(A115:A290,"WAGE") and it's returning only 21
occurences, when I know there's more than 50. I thought it had to do with
the cell format, because she had some of the strings formatted as numbers and
some as general, but when I changed all of the strings to general, it still
returns only 21 occurences. I've tried copying/pasting the function she used
for other strings that are calculating correctly, and I've tried entering the
function manually. Both to no avail. She is using Excel 2007 under Windows
XP SP3. I opened the spreadsheet on my Vista SP2 computer and get the same
results that she does. Any help would be greatly appreciated.

 

[Mike H]

Hi,

The usual culprit is rogue spaces, try this

=SUMPRODUCT(--(TRIM(A115:A290)="wage"))

Mike

 

[ShaneDevenshire]

Hi

Change WAGE to *WAGE"

 

[LilOlLady]

I tend to agree with the last post.
I would add that if the data is imported, it may have allsorts of code
attached form the source. Perhaps you should also use the CLEAN function.
Using boh the TRIM and CLEAN functions should ensure WYSIWYG.

 

[Mike H]

or better still *wage*

Hi

Change WAGE to *WAGE"