+ Reply to Thread
Results 1 to 6 of 6

Need Help Counting Unique Values If 2 Columns Match

  1. #1
    Registered User
    Join Date
    02-09-2016
    Location
    Leeds, England
    MS-Off Ver
    2013
    Posts
    9

    Need Help Counting Unique Values If 2 Columns Match

    I have trawled the forum and the internet and cannot find a solution. I am likely being dim as I am not an Excel expert so here goes...

    I have data in 2 columns for students in a school who have had a detention

    Column A has the student name
    Column B has the week in the term they have detention

    it looks something like below:

    Student Week
    Anna 1
    Matt 2
    Anna 2
    Steve 3
    Anna 4
    Matt 4
    Anna 4

    I need to be able to count the number of different values in column A, so here it would be 3 as there are 3 different students. I also need to be able to count the number of different values in column A in the different weeks. as I need to be able to say how many different students have had detentions each week, e.g.1 in week 1, 2 in week 4 because Anna had 2 in one week.

    It would be most appreciated if anyone has any pearls of wisdom. Ta muchly in advance

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,466

    Re: Need Help Counting Unique Values If 2 Columns Match

    You might want to play with pivot tables. (Insert>Pivot Table)
    Put Student as a row label and as a value to see total students and how many times each was in dentention.

    Just pull Student out of row label and put Week in and you'll see how many students were in detention each week.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-09-2016
    Location
    Leeds, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Need Help Counting Unique Values If 2 Columns Match

    Quote Originally Posted by ChemistB View Post
    You might want to play with pivot tables. (Insert>Pivot Table)
    Put Student as a row label and as a value to see total students and how many times each was in dentention.

    Just pull Student out of row label and put Week in and you'll see how many students were in detention each week.
    Thanks for the reply...

    I have tried this as you suggested but it does not seem to give me what I need, although it may still be me being stupid. It seems to still be totalling the number of detentions in a week not the number of individuals who had a detention. In my example, 7 detentions were issued but to only 4 individuals and it is this information I need to summarise, I need to replicate something like below ... (using my data above).

    Number of students issued with a detention.
    Week 1-------Week 2-------Week 3-------Week 4-------Total
    1--------------2--------------1--------------2--------------3
    Last edited by Xeba37; 02-09-2016 at 06:14 PM.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need Help Counting Unique Values If 2 Columns Match

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    Student
    Week
    Unique Students
    By Week
    2
    Anna
    1
    3
    1
    1
    3
    Matt
    2
    2
    2
    4
    Anna
    2
    3
    1
    5
    Steve
    3
    4
    2
    6
    Anna
    4
    7
    Matt
    4
    8
    Anna
    4
    9
    ------
    ------
    ------
    ------
    ------
    ------


    If you have "a lot" of data we can use a more efficient formula.

    This formula entered in D2:

    =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

    This array formula** entered in F2 and copied down:

    =SUM(IF(FREQUENCY(IF(B$2:B$8=E2,MATCH(A$2:A$8,A$2:A$8,0)),ROW(A$2:A$8)-ROW(A$2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    02-09-2016
    Location
    Leeds, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Need Help Counting Unique Values If 2 Columns Match

    Quote Originally Posted by Tony Valko View Post
    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    Student
    Week
    Unique Students
    By Week
    2
    Anna
    1
    3
    1
    1
    3
    Matt
    2
    2
    2
    4
    Anna
    2
    3
    1
    5
    Steve
    3
    4
    2
    6
    Anna
    4
    7
    Matt
    4
    8
    Anna
    4
    9
    ------
    ------
    ------
    ------
    ------
    ------


    If you have "a lot" of data we can use a more efficient formula.

    This formula entered in D2:

    =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

    This array formula** entered in F2 and copied down:

    =SUM(IF(FREQUENCY(IF(B$2:B$8=E2,MATCH(A$2:A$8,A$2:A$8,0)),ROW(A$2:A$8)-ROW(A$2)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Fantastic, worked a treat. Thank you so much.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Need Help Counting Unique Values If 2 Columns Match

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] counting unique values into tw odifferent columns
    By joannelittell in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-10-2013, 10:15 AM
  2. [SOLVED] counting unique values in col A against unique value in column B
    By greyscale in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2013, 08:43 AM
  3. Replies: 1
    Last Post: 02-18-2013, 01:46 AM
  4. Replies: 3
    Last Post: 06-24-2012, 01:19 PM
  5. [SOLVED] Counting Unique Values Across 2 Columns
    By mrfloopa in forum Excel General
    Replies: 3
    Last Post: 06-12-2012, 02:38 PM
  6. Replies: 0
    Last Post: 03-08-2012, 12:18 PM
  7. Replies: 6
    Last Post: 10-18-2011, 07:34 PM
  8. Replies: 2
    Last Post: 02-10-2005, 10:06 AM

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