# Excel: count unique instances across two columns

microsoft excelmicrosoft-excel-2010worksheet-function

I have a spreadsheet that has a column for cell sites and another column for frequency like this:

``````SITE      FREQUENCY
MA1084    687
MA1084    687
MA1084    687
MA1084    4382
MA1084    4382
MA1084    4382
MA1096    4358
MA1096    4358
MA1096    4358
MA1096    687
MA1096    687
MA1096    687
MA1096    4382
MA1096    4382
MA1096    4382
``````

I'd like a formula that will add up all the unique combinations of SITE and FREQUENCY. My expected output is below. Basically I want to know how many unique frequencies each cell site has.

``````SITE      FREQUENCY COUNT
MA1084    687       2
MA1084    687       2
MA1084    687       2
MA1084    4382      2
MA1084    4382      2
MA1084    4382      2
MA1096    4358      3
MA1096    4358      3
MA1096    4358      3
MA1096    687       3
MA1096    687       3
MA1096    687       3
MA1096    4382      3
MA1096    4382      3
MA1096    4382      3
``````

I can do this with a pivot table but am looking for a formula based solution. Any help is appreciated.

One solution is to use a helper column. While not always ideal, it depends on how you need the output displayed. Assuming `Site` and `Frequency` are in columns A and B, then in the concatenate column type: `=A2&B2`. Then in the `Count` column type: `=COUNTIF(C:C,C2)`. Then you get the following:

``````SITE    FREQUENCY   Concatentae COUNT
MA1084  687 MA1084687   3
MA1084  687 MA1084687   3
MA1084  687 MA1084687   3
MA1084  4382    MA10844382  3
MA1084  4382    MA10844382  3
MA1084  4382    MA10844382  3
MA1096  4358    MA10964358  3
MA1096  4358    MA10964358  3
MA1096  4358    MA10964358  3
``````