Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Excel Duplicates Count Once (counting different letters) (https://www.askmehelpdesk.com/showthread.php?t=501026)

  • Aug 23, 2010, 12:11 PM
    RodriguisIUR
    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?
  • Aug 24, 2010, 05:03 AM
    JBeaucaire

    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
  • Aug 24, 2010, 08:58 AM
    RodriguisIUR

    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... :)

  • All times are GMT -7. The time now is 11:09 AM.