Counting unique entries in several ranges

[chastst]

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

 

[Domenic]

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]

Hi chastst

This is one possible solution. Please try:

=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]

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]

Hi again

The same formula with one more range

=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]

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]

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

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