+ Reply to Thread
Results 1 to 10 of 10

total count of non-blank cell(s) and unique values

  1. #1
    Registered User
    Join Date
    04-28-2010
    Location
    TX
    MS-Off Ver
    Excel 2007
    Posts
    5

    total count of non-blank cell(s) and unique values

    Good afternoon - I have the spreadsheet bleow (it's also attached for better layout), and what I want it to get is the total count of non-blank cell from the answer(column B) and only count the answer once for those users that are listed multiple times (columnA).So my total count fro ColumnB should be 18. Please let me know how can I accomplish this.
    Thank you in advance!

    User Answer
    JOSE Yes
    MARIA No - Other Reason
    MARY No - Other Reason
    SHERYL K No - Other Reason
    VIGGO J No - Other Reason
    IGNACIO No - Other Reason
    ISAAC No - Other Reason
    LAURA Yes
    GUADA No - Other Reason
    MERIC No - Other Reason
    JUAN No - Other Reason
    OLGA No - Other Reason
    ARTHUR Yes
    CARLA Yes
    JOAN No - Other Reason
    LILLIE Yes
    LILLIE Yes
    DOROTHY No - Other Reason
    JOHN C
    PABLO No - Other Reason
    PABLO No - Other Reason
    PABLO No - Other Reason
    Attached Files Attached Files
    Last edited by NBVC; 06-09-2010 at 01:54 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: total count of non-blank cell(s) and unique values

    Perhaps something like:

    =COUNT(1/FREQUENCY(IF(A2:A23<>"",IF(B2:B23<>"",MATCH(A2:A23&B2:B23,A2:A23&B2:B23,0))),ROW(A2:A23)-ROW(A2)+1))

    adjust ranges to suit and confirm with CTRL+SHIFT+ENTER not just ENTER.

    another less complicated way may be to concatenate the column A and B items into Column C with fomrula like: =A2&B2 copied down

    Then apply formula: =SUMPRODUCT((C2:C23<>"")/COUNTIF(C2:C23,C2:C23&"")) with just ENTER
    Last edited by NBVC; 04-28-2010 at 04:29 PM. Reason: adding 2nd option
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-28-2010
    Location
    TX
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: total count of non-blank cell(s) and unique values

    Hi there - I tried the 1st formula, it gave me a total of 19, I'm getting one more than expected. I'm attaching the spreadsheet...please let me know what I'm doing wrong. Thanks again!
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    122

    Re: total count of non-blank cell(s) and unique values

    LILIE on cell A17 has one space at the end of the word, that makes it different than LILIE in cell A18.

    Regards

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: total count of non-blank cell(s) and unique values

    FWIW, SUMPRODUCT wise you could also use the below without need for concatenation:

    =SUMPRODUCT(--($A$2:$A$23<>""),--($B$2:$B$23<>""),--(MATCH($A$2:$A$23&"@"&$B$2:$B$23,$A$2:$A$23&"@"&$B$2:$B$23,0)=(ROW($A$2:$A$23)-ROW($A$2)+1)))

  6. #6
    Registered User
    Join Date
    04-28-2010
    Location
    TX
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: total count of non-blank cell(s) and unique values

    I've corrected the cell A17 and tried the concatenate formula, it works fine, except when I get 2 of the same users with 2 different answer on column B, it's counting both answers...I only want to count one non-blank answer for each user. Is there a way to do this? (see sample below-the total shows 19, should be 19..it's counting Lillie's answer twice). thanks!

    USER ANSWER CONCATENATED
    JOSE Yes JOSEYes
    MARIA No - Other Reason MARIANo - Other Reason
    MARY No - Other Reason MARYNo - Other Reason
    SHERYL K No - Other Reason SHERYL KNo - Other Reason
    VIGGO J No - Other Reason VIGGO JNo - Other Reason
    IGNACIO No - Other Reason IGNACIO No - Other Reason
    ISAAC No - Other Reason ISAACNo - Other Reason
    LAURA Yes LAURA Yes
    GUADA No - Other Reason GUADA No - Other Reason
    MERIC No - Other Reason MERICNo - Other Reason
    JUAN No - Other Reason JUAN No - Other Reason
    OLGA No - Other Reason OLGA No - Other Reason
    ARTHUR Yes ARTHURYes
    CARLA Yes CARLAYes
    JOAN No - Other Reason JOANNo - Other Reason
    LILLIE Yes LILLIE Yes
    LILLIE No - Other Reason LILLIENo - Other Reason
    DOROTHY No - Other Reason DOROTHY No - Other Reason
    JOHN C
    PABLO No - Other Reason PABLONo - Other Reason
    PABLO No - Other Reason PABLONo - Other Reason
    PABLO No - Other Reason PABLONo - Other Reason

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: total count of non-blank cell(s) and unique values

    Are you looking to calculate the number of unique names that have offered at least one answer ?

    FWIW - when concatenating strings it's generally a good idea to concatenate with a delimiter (that does not otherwise appear in the strings), eg:

    C2: =A2&"@"&B2
    copied down

    This reduces risk of reporting false positive matches in the COUNT.


    Better to post sample files by the way...
    Last edited by DonkeyOte; 04-30-2010 at 02:35 AM. Reason: re-phrased

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: total count of non-blank cell(s) and unique values

    Quote Originally Posted by esanchezz
    I only want to count one non-blank answer for each user
    In which case, adopting the concatenation approach - I would suggest you modify your concatenation to:

    C2: =$A2&"@"&($B2<>"")
    copied down to C23

    At which point your formula becomes:

    =SUMPRODUCT(($B$2:$B$23<>"")/COUNTIF($C$2:$C$23,$C$2:$C$23&""))

    And based on your data I would assume the correct result to be 17 - ie their are 17 unique names in the list that have offered at least one answer
    (Dorothy, John C though unique have not offered an answer and are thus discounted).

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: total count of non-blank cell(s) and unique values

    And again make sure there are no lingering spaces at the end of any of the entries in Columns A and B

  10. #10
    Registered User
    Join Date
    04-28-2010
    Location
    TX
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: total count of non-blank cell(s) and unique values

    This is SOLVED, but I'm not able to get to my original post to mark it SOLVED. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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