count number of unique text instances in a range

Kempy

Board Regular
Joined
Feb 18, 2013
Messages
51
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.
 
Upvote 0
=SUMPRODUCT((H12:I17<>"")/(COUNTIF(H12:I17,H12:I17&"")))</SPAN>

Use CTRL+SHIFT+ENTER to confirm the formula
 
Upvote 0
=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...
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top