Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Cool Unanswered: Need Help with Concatenate and Countif

    I need your help in calculation a number and value.
    The case is-
    Column A contains Name
    Column B contains Numbers
    Now, i need to concatenate A&B and find the unique data of concatenate using countif. How do i do this in single cell using a single formula?
    Last edited by Bala21; 02-23-12 at 03:28.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    Now, i need to concatenate A&B and find the unique data of concatenate using countif. How do i do this in single cell using a single formula?
    Why do you ask for it to be done exactly that way? You can't do it in a single formula using countif and concatenate.

    If it must be done in a single cell then this formula will give you the distinct count:
    Code:
    =SUM(IF(FREQUENCY(IF(A2:A100&B2:B100<>"",
        MATCH("~"&A2:A100&B2:B100,A2:A100&B2:B100,0)),
        ROW(A2:A100)-ROW(A2)+1),1))
    It is an array formula so it must be completed with CTRL+SHIFT+ENTER, not just enter.

Posting Permissions

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