Search code examples
excelexcel-formulaworksheet-functioncountif

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.


Solution

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