CODEWITHSUNDEEP
excelexcel-formulaworksheet-functioncountif
Padawan
Padawan

Reputation: 313

Count of unique values in multiple columns

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.

Upvotes: 1

Views: 1272

Answers (1)

pnuts
pnuts

Reputation: 59442

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.

Upvotes: 3

Related Questions