Count of unique values in multiple columns

Question

What I need is probably best described in an example. It's a bit different from the group functionality and also the PivotTable in Excel, because I want it to show up in the data row itself (and not off to the side or below, etc.). Given a table like:

Fruit   Color   Farmer
Banana  Yellow  Smith
Banana  Yellow  Smith
Apple   Yellow  James
Apple   Yellow  James
Apple   Green   Smith
Banana  Yellow  James

I want to take the first two columns and give the count of rows that have the same values (regardless of the values in the other columns). So for my example, I would get:

Fruit   Color   Count   Farmer
Banana  Yellow  3       Smith
Banana  Yellow  3       Smith
Apple   Yellow  2       James
Apple   Yellow  2       James
Apple   Green   1       Smith
Banana  Yellow  3       James

My preference would be an Excel formula (or even a built in function) as opposed to VBA.

pnuts · Accepted Answer · 2015-03-19 03:01:17Z

3

Assuming Fruit is in A1, please try in C2 (having made room for it):

=COUNTIFS(A:A,A2,B:B,B2)

and copy down to suit.

answered Mar 19, 2015 at 3:01

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count of unique values in multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related