 |
|
| Browse | Ask | Answer | Search | Join/Login |
|
|
||||
|
Excel Duplicates Count Once (counting different letters)
Hello,
In A1:A10 I have : ( A,A,A,A,B,B,C ) (only 7 values, the rest is blank but it can have letters too if I want) I want to count the amount of different letters I have in this area minus the last letter. So, in this case I know that the result would be 1-A and 1-B, the C doesn't count. The last letter is summed apart of this formula that I want. If I have ( A,B,B,B,B,A ) , The first A will not count because it exists in as final letter. So, I just want to count if a letter appears more than once beside s of being final letter. If it appears more than once, then its counting is only 1. What I want : "to find if the last letter is equal to some other letter, and if so, don't count it, and then count the amount of different letters" In the first example the result would be = 2 ( 1A + 1B ; C is unique, so does not count) In the second example the result would be = 2 ( 1A + 1B ; Last A does not count because there are more than 1A) Is it possible a formula to find this and show the result in one cell? |
||||
|
||||
|
Are we counting actual single-letter text strings? Each cell holds a single letter text string and you want to know how many of them there are except for the last one? Or is the actual need much more complicated and you've dumbed the sample question down too far? Try this formula on an empty cell: =SUMPRODUCT((A1:A10<>"") / COUNTIF(A1:A10, A1:A10&"")) - 1 |
||||
|
||||
|
Thanks JB! You have been a great helper... Well, I tried your option but didn't work... but I found other way to resolve it with help of other cell, it works perfectly, as I wanted! The goal was: - In 10 cells with cronological appearance count the different appearances of letters ( 1 A , 1 B, 1 C, even if they appear more than once ) - " In "n" letters that appeared , how many are A's, B's, C's and that are different from the last one?" - "The last letter is always counted with a bonus of 1 point" - "Each letter must appear twice or more to be part of the total sum when are not equal to the last letter"; "If they appear once and are not in the last position, can be summed once" Well, I don't know how to explain better, it's a little confusing, but somehow I managed the formula summing many Countif's within IF's and with AND's too. It's working! But thanks anyway! I'm a little unexperienced with excel and its formulas... :) |
||||
| Question Tools | Search this Question |
Add your answer here.
Check out some similar questions!
I am trying to count a large group of cells that are colored depending on the data. For example: I want to count the number of red cells which are faults. I tried the countif but not sure how to set the criteria. Thanks for any help.
Below are the columns of my excel 2007 spreadsheet... red blue yellow 1 2 3 3 1 5 2 5 4 1 2 4 I am trying to COUNT the number of times a specified values occur in 2 instances...
I need to count how many of each item is contained in a column of data (for instance, 4000 rows of data, 100 different items, but multiples of each item - how many Apples, Oranges, Bananas, etc. of each?). How do I do that in Excel 2007?
Hello, I wish to be able to count multiple cells which are not in a row or column adjacent to each other. If know you can use the IF formula to reference any cells you wish without them having to be in a range. How can I count cells specifying a criteria that has to be true which are not in a...
In November 2004 Leif 5233 wrote the following regarding counting cells highlighted in a certain colour: ------------------ Create this function: Function CountCol(SumRange As Range, intColor As Integer) As Integer Dim I As Integer Dim Cell As Range Set SumRange =...
View more questions Search
|
