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.

Best Answer

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

Related Solutions

Excel – How to count unique comma-separated values in Excel 2010

If you can add some intermediate formulas to your sheet, here's a possible solution.

Based on the ranges in your example:

Put in cell N123 This returns the part to the left of , or the whole cell if no ,

=IFERROR(LEFT($M123,FIND(",",$M123)-1),M123)

Put in cell O123 This returns the part to the right of , or - if no ,

=IFERROR(TRIM(MID($M123,FIND(",",$M123)+1,999)),"-")

Put in cell P123 This returns 1 if cell N123 is unique in the list, plus 1 if cell O123 is unique

=AND(ROW()+1=ROW($N$123:$N$127)+MATCH($N123,$N$123:$N$127,0),$N123<>"-")
+AND(ROW()=IFERROR(MATCH($O123,$N$123:$N$127,0),""),$O123<>"-")
+AND(ISNA(MATCH($N123,$N$123:$N$127,0)),ROW()+1=IFERROR(ROW($N$123:$N$127) 
    +MATCH($N123,$O$123:$O$127,0),FALSE),$N123<>"-")
+AND(ISNA(MATCH($O123,$N$123:$N$127,0)),ROW()+1=ROW($N$123:$N$127)
    +MATCH($O123,$O$123:$O$127,0),O123<>"-")

Copy cells N123..P123 down to N123..P127

The number of unique items is =SUM(P123:Q127)

This assumes there are at most 2 items in each data cell. If this is not the case you will need to add more intermediate columns, and extend the formula in O123 accordingly. If there are many more than 2, this will quickly get out of hand.

FWIW, you say a VBA solution is not posible, but is a VBA user defined function ok?

It would look something like =CountUnique(M123:M127)

Excel – How to count unique values in one column based on criteria in two other columns

Would you consider adding a formula that simply comonbines values of both columns, and another that counts new/unique values?

Formula for E =$C:$C&$D:$D

Formula for F =IF(COUNTIF(E$1:E2,E2)=1,1,0) ( this is example fopr cell( F2)

country  |  Preview  |  Live  |  On Demand | Combined | New/unique
GB       |  NO       |  NO    |  YES       | NOYES    | 1
GB       |  NO       |  YES   |  YES       | YESYES   | 1
ES       |  NO       |  YES   |  YES       | YESYES   | 0
DE       |  NO       |  NO    |  YES       | NOYES    | 0
FR       |  NO       |  NO    |  YES       | NOYES    | 0
US       |  NO       |  NO    |  YES       | NOYES    | 0

Best Answer

Related Solutions

Excel – How to count unique comma-separated values in Excel 2010

Excel – How to count unique values in one column based on criteria in two other columns

Related Question