Need Help Counting Unique Values If 2 Columns Match

1. 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. 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.

3. Re: Need Help Counting Unique Values If 2 Columns Match

Originally Posted by ChemistB
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.

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

4. 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.

5. Re: Need Help Counting Unique Values If 2 Columns Match

Originally Posted by Tony Valko
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. Re: Need Help Counting Unique Values If 2 Columns Match

You're welcome. Thanks for the feedback!

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

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

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