COUNTIF in a list that contains numbers formatted as text
Results 1 to 4 of 4

Thread: COUNTIF in a list that contains numbers formatted as text
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIF in a list that contains numbers formatted as text

    Excel 2013 64-Bit: I have a list of text that will NOT count correctly (to get the alphabetical order) and, for the first time I know of, I am unable to find any previous discussion anywhere that is relevant.
    A B C D
    1 ITEM ORDER FORMULATEXT CORRECT ORDER
    2 1062287 0 =COUNTIF($A$2:$A$6,"<="&$A2) 1
    3 1062740 Folio 2 =COUNTIF($A$2:$A$6,"<="&$A3) 2
    4 1063209 Part 3 =COUNTIF($A$2:$A$6,"<="&$A4) 3
    5 1063648 0 =COUNTIF($A$2:$A$6,"<="&$A5) 4
    6 1063832 Folio 5 =COUNTIF($A$2:$A$6,"<="&$A6) 5



    Column A and the values within are formatted as TEXT.. When that alone didn't work, I added ' to the beginning of the text in A2 and A5. The ORDER will NOT calculate correctly no matter what I try because it insists on treating A2 and A5 as values. Any suggestions other than adding space or concatenating it with another text value (which I already confirmed does work but SHOULDN'T BE NECESSARY)?

    Thanks!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,105
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: COUNTIF in a list that contains numbers formatted as text

    Try...

    =SUMPRODUCT(--($A$2:$A$6&""<=A2&""))

    instead.
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Aug 2017
    Posts
    342
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: COUNTIF in a list that contains numbers formatted as text

    =sumproduct(($a$2:$a$6<=$a2)*1)

  4. #4
    New Member
    Join Date
    Jul 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF in a list that contains numbers formatted as text

    Neither option seem to give the right answer. I ended up having to create a different field to concatenate some text in front of the real sorting target to get it to work. All the years I've worked with Excel -- the text/number/number/text inconsistencies and/or auto-treatment STILL finds a new way to drive me crazy!!!

    THANKS for such a quick response though I had already moved on and took this long to reply!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •