# Counting unique entries in several ranges

#### chastst

##### New Member
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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!

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

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

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

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!

If you have the latest morefunc.xll add-in installed...

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

Replies
1
Views
111
Replies
1
Views
189
Replies
0
Views
227
Replies
5
Views
273
Replies
2
Views
232

1,219,960
Messages
6,151,163
Members
451,012
Latest member
OH650R

### 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