+ Reply to Thread
Results 1 to 6 of 6

exl 2010 countif, criteria=final digit in string is not present in other characters

  1. #1
    Registered User
    Join Date
    11-26-2012
    Location
    USA
    MS-Off Ver
    Excek 2010
    Posts
    7

    exl 2010 countif, criteria=final digit in string is not present in other characters

    Forgive my title, I wasn't sure how to write it down.

    I'm creating an excel sheet to help serve as a probability calculator and I'm in need of a fairly crazy formula. I've drastically dumbed down my excel file for the sake of size but the principle is still the same

    in the attached file you'll see 47 rows (range A1:F47) that represents a few of the possible outcomes you could have when rolling 6 dice. I need a countif formula that does the following:

    note: range a1:f47 is named "PROB"
    look at every cell in range PROB and only count the cells where the final character in each cell does not equal any of the other characters in it's respective cell.

    So, looking at the attachment the countif function would not count cell a1, because the 6th character (a 1) matches at least one of the other 5 characters in cell a1. However, it would count cell b2 because the final character in that cell (number 2) does not match any of the other 5 characters in that cell.

    If you need to make the cells in this range text or numbers makes no difference to me. I just need it to count all of the values where the final character in a cell does not match the other characters in that respective cell.

    I have theories on how this argument should be constructed but everything I've tried has not worked.

    Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: exl 2010 countif, criteria=final digit in string is not present in other characters

    A bit of a roundabout way, but would the attached work?
    The total number is listed in V2.
    Attached Files Attached Files
    Please click the * icon below if I have helped.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: exl 2010 countif, criteria=final digit in string is not present in other characters

    Try using this formula

    =SUMPRODUCT((LEN(SUBSTITUTE(A1:F47,RIGHT(A1:F47),""))=5)+0)

    That works specifically for your scenario where every cell has 6 characters. For variable character length you could make that

    =SUMPRODUCT((LEN(SUBSTITUTE(A1:F47,RIGHT(A1:F47),""))=LEN(A1:F47)-1)+0)

    both formulas give 162 with your example (I think Melvin's formula is counting those cells where the last character is repeated)
    Audere est facere

  4. #4
    Registered User
    Join Date
    11-26-2012
    Location
    USA
    MS-Off Ver
    Excek 2010
    Posts
    7

    Re: exl 2010 countif, criteria=final digit in string is not present in other characters

    daddlylonglegs

    I consider myself an excel novice and I was wondering if you could help me understand the logic behind your formula?

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: exl 2010 countif, criteria=final digit in string is not present in other characters

    No problem,

    Lets look at a single cell - imagine D3 contains 123456 - you want to ascertain whether the last character, 6, occurs anywhere else in that cell - you can get the last character using RIGHT function, i.e.

    =RIGHT(D3) = "6"

    Now if I use SUBSTITUTE to replace any 6 in D3 with a blank [""] then I can do that like this

    =SUBSTITUTE(D3,RIGHT(D3),"")

    so because that replaces all instances in D3 of RIGHT(D3) with a blank it converts 123456 to "12345"

    Why is that useful? Because if the rightmost value only occurs once in D3 the resultant value will always have 5 characters, if 6 occurred anywhere else in D3 the result would be < 5 characters, e.g. if D3 was 123656 then =SUBSTITUTE(D3,RIGHT(D3),"") = "1235"

    ....so we can test the resultant cell length with LEN function, i.e.

    =LEN(SUBSTITUTE(D3,RIGHT(D3),""))=5

    Now that will return TRUE or FALSE depending on whether the last character in D3 repeats or not, we can make it 1 or 0 by doing this

    =(LEN(SUBSTITUTE(D3,RIGHT(D3),""))=5)+0

    Now, finally we can replace D3 with a range of cells, so

    =(LEN(SUBSTITUTE(A1:F47,RIGHT(A1:F47),""))=5)+0

    will return a 6x47 matrix of 1/0 values, so for your calculation we just need to sum those. SUM can be used like this

    =SUM((LEN(SUBSTITUTE(A1:F47,RIGHT(A1:F47),""))=5)+0)

    ....but that formula needs to be confirmed with CTRL+SHIFT+ENTER so my preference is to use SUMPRODUCT in place of SUM to avoid "array entry" (although it only sums the values as before)

  6. #6
    Registered User
    Join Date
    11-26-2012
    Location
    USA
    MS-Off Ver
    Excek 2010
    Posts
    7

    Re: exl 2010 countif, criteria=final digit in string is not present in other characters

    sorry for waiting so long to return. Your formula works great and is quite clever. Thank you for explaining it to me

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1