# count number of unique text instances in a range

#### Kempy

##### Board Regular
Hi All,

I have been using the following formula to count the number of unique text occurances in a list.

=SUM(IF(FREQUENCY(IF(LEN(H12:H37)>0,MATCH(H12:H37,H12:H37,0),""), IF(LEN(H12:H37)>0,MATCH(H12:H37,H12:H37,0),""))>0,1))

however i now want to do the same but for the range h12:u37

Is this possible with a siliar forumla, i have tried amending the above but it just returns N/A

Any help greatly appreciated.

Kind Regards

Alistair

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Kempy,
This gets complicated. Match can only look up items in a contiguous range. In other words, you can only match a lookup array in one column or row. You would have to manipulate the array results to get a multiple column (and row) unique identifier. The formula will get very long if you want to accomplish this, but it can be done.

=SUM(IF(FREQUENCY(IF(LEN(H12:I17)>0,IF(IFERROR(MATCH(H12:I17,H12:H17,0),0)=0,MATCH(H12:I17,I12:I17,0)+ROWS(H12:I17),MATCH(H12:I17,H12:H17,0)),""),IF(LEN(H12:I17)>0,IF(IFERROR(MATCH(H12:I17,H12:H17,0),0)=0,MATCH(H12:I17,I12:I17,0)+ROWS(H12:I17),MATCH(H12:I17,H12:H17,0)),""))>0,1))

That is for the range H12:I17. For each additional column, you would have to add another layer of if then.

If someone else knows a better way to solve this (I would imagine this formula would be EXTREMELY long to get to column U), I would love to hear it.

Are we talking VBA, or a cell formula?

=SUMPRODUCT((H12:I17<>"")/(COUNTIF(H12:I17,H12:I17&"")))</SPAN>

Use CTRL+SHIFT+ENTER to confirm the formula

=SUMPRODUCT((H12:I17<>"")/(COUNTIF(H12:I17,H12:I17&"")))

Use CTRL+SHIFT+ENTER to confirm the formula

NIICE!!! I dont even think this needs a ctrl shift enter as sumproduct can handle arrays...

=SUMPRODUCT((H12:I17<>"")/(COUNTIF(H12:I17,H12:I17&"")))</SPAN>

Use CTRL+SHIFT+ENTER to confirm the formula

Simple yet brilliant!

Replies
4
Views
118
Replies
0
Views
237
Replies
1
Views
78
Replies
5
Views
192
Replies
9
Views
313

1,196,516
Messages
6,015,678
Members
441,915
Latest member
sm Hussaini

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back