View original (French)

Nb.si does not give me the correct result.

moaxtr Posted messages 3 Status Member -  
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   -
Hello,

I'm using the COUNTIF function on a range of cells (some with numbers, others with text) that is already used by other functions apart from COUNTIF, but it's not giving me the correct result! Could there be a conflict between the numbers and the text causing the function to not execute correctly? What is the solution, please? Thank you in advance.

Configuration: Windows 7 / Firefox 5.0

4 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    As you might guess, a number is a number, and a text is a text.
    "45" is different from 45

    The best thing to do is to convert all your numbers to numeric format.
    - copy an empty cell
    - select the range
    - special paste addition

    Your texts must be cleared of spaces and other unwanted characters beforehand (and have the correct decimal separator)

    Eric
    1
  2. moaxtr Posted messages 3 Status Member
     
    Thank you eriiic,

    However, since my table is already composed, I can't see myself doing it all over again, and when I copy the old values (text and numbers) and paste them into the new sheet (with cells formatted as numbers, decimal, etc., as you mentioned in your response), it still doesn't work or works poorly. In fact, even though the range and its cells are correctly adjusted, it always shows me an erroneous result.

    I have a row made up of 12 columns where I'm trying to find out how many times the word "Atraso" appears, and I'm starting to go crazy because count.if only finds 3 instead of the 4 that are actually there. Could my Excel software have problems??
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      When I copy the old values (text and numbers) and then paste them into the new sheet (with cells formatted as numbers, decimal, etc., as you mentioned in your reply)
      I never said that, I said convert to numeric by giving you a technique, which has nothing to do with the cell format...

      But since it's text you want to count, you can forget it.
      If Excel finds fewer, it's because there are fewer.
      There are spaces or other stray characters that are deceiving you, but not Excel.
      Try with =COUNTIF(A1:E1,"*atraso*")

      eric
      0
  3. moaxtr Posted messages 3 Status Member
     
    Awesome, it works :-) I'm super happy, I've been racking my brain for 3 days trying to find the solution, then I sign up for this forum and I get the answer, thank you very much Eric, I can finally get back to work... so I don't end up looking stupid, can you tell me what the little stars change in the formulas? A+
    0
  4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    * means: any string of characters
    So be careful sdcfsdvfAtrasogfhn will count for 1
    eric
    0