Counting unique entries in several ranges

chastst

New Member
Joined
Dec 10, 2002
Messages
21
I am attempting to count unique entries in a worksheet across several ranges. I used the cpearson formula of

=SUM(IF(FREQUENCY(IF(LEN($K$4:$K$31)>0,MATCH($K$4:$K$31,$K$4:$K$31,0),""),IF(LEN($K$4:$K$31)>0,MATCH($K$4:$K$31,$K$4:$K$31,0),""))>0,1))

and that works fine. But I need to look at C4:C17 and C26:C43 at the same time to cound unique values. Can someone help me with what the formula should look like?

TIA
chastst
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
If the data contains numerical values, try...

=SUM(IF(FREQUENCY((C4:C17,C26:C43),(C4:C17,C26:C43)),1))

Otherwise, try...

=SUM(IF(FREQUENCY(IF(ISNA(MATCH(ROW(C4:C43),{18,19,20,21,22,23,24,25},0)),IF(C4:C43<>"",MATCH(C4:C43,C4:C43,0))),ROW(C4:C43)-ROW(C4)+1),1))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi chastst

This is one possible solution. Please try:

Code:
=SUM(IF(C4:C17 <>"",1/(COUNTIF(C4:C17,C4:C17 )+COUNTIF(C26:C43,C4:C17 ))))+SUM(IF(C26:C43 <>"",1/(COUNTIF(C4:C17,C26:C43 )+COUNTIF(C26:C43,C26:C43 ))))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

You can simplify the formula if you are sure that there will never be blank cells in those ranges.

Hope this helps
PGC
 

chastst

New Member
Joined
Dec 10, 2002
Messages
21
Need Original Range Too

I haven't tried them yet, but these look like they will work, but I also need the original K4:K32 range.

Thanks
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi again

The same formula with one more range

Code:
=SUM(IF(C4:C17 <>"",1/(COUNTIF(C4:C17,C4:C17 )+COUNTIF(C26:C43,C4:C17 )+COUNTIF(K4:K32,C4:C17 ))))
+SUM(IF(C26:C43 <>"",1/(COUNTIF(C4:C17,C26:C43 )+COUNTIF(C26:C43,C26:C43 )+COUNTIF(K4:K32,C26:C43 ))))
+SUM(IF(K4:K32  <>"",1/(COUNTIF(C4:C17,K4:K32 )+COUNTIF(C26:C43,K4:K32 )+COUNTIF(K4:K32,K4:K32 ))))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.

You can simplify the formula if you are sure that there will never be blank cells in those ranges.

Hope this helps
PGC
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
Re: Need Original Range Too

I haven't tried them yet, but these look like they will work, but I also need the original K4:K32 range.

Thanks

If the data contains numerical values, try...

=SUM(IF(FREQUENCY((C4:C17,C26:C43,K4:K32),(C4:C17,C26:C43,K4:K32)),1))

Otherwise, it looks like pgc01 has offered a nice solution...

Hope this helps!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
If you have the latest morefunc.xll add-in installed...

=COUNTDIFF(ARRAY.JOIN(C4:C17,C26:C43,K4:K32))
 

Forum statistics

Threads
1,141,678
Messages
5,707,782
Members
421,527
Latest member
Tamiwsw

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
Top