Using Pivot Table to count how many times a value appears in a data set

Question

I have a data set four columns wide (columns being People 1, People 2, People 3, People 4) with a person's name in each column. I want to set up a table that shows each name of a person with how many times they show up across the four columns (total). The names could show up in any four of the columns, but only once per row.

How can I do that? Is a pivot table the most efficient way?

Answer 1

Although I like PivotTables I would suggest instead a formula such as:

=COUNTIF(A:D,F2)  

in Row2 copied down to suit, where I have assumed your four columns of People are A:D and F2 and down is populated with a list of unique names. The unique list might be constructed by copying the content of ColumnA, appending the content of ColumnB, etc and then applying Remove Duplicates to the copied content.

Answer 2

You can still use a pivot table but you will need to add a row first:

Assume your names are in columns A-D

Then add the following

formula to column E

=AND(A1=B1,B1=C1,C1=D1)

---

A      B      C      D      E
bob    Bob    Bob    Bob    TRUE
bob    Bob    Mike   Bob    FALSE