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

1. ## 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  Register To Reply

2. ## 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.  Register To Reply

3. ## 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)  Register To Reply

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

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

5. ## 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)  Register To Reply

6. ## 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  Register To Reply

##### Users Browsing this Thread

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

#### Tags for this Thread #### 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