Count Unique Numerical Values that begin with

[pauln93]

Hi,

I'm making a database right now that need a formula for counting unique numerical values that begin with a certain number, because of the amount of duplicates. All the formula I tried doing myself have resulted in errors

 

[Marcelo Branco]

Welcome to Mr Excel forum

Could you provide a small data sample (~10 rows) along with criteria and expected result?

M.

 

[pauln93]

Hi,

This is the set of 10, and I'm looking to say count things that begin with 8 but doesn't include duplicates

[TABLE="width: 109"]
<colgroup><col></colgroup><tbody>[TR]
[TD]8404[/TD]
[/TR]
[TR]
[TD]8404[/TD]
[/TR]
[TR]
[TD]8404[/TD]
[/TR]
[TR]
[TD]8449[/TD]
[/TR]
[TR]
[TD]8450[/TD]
[/TR]
[TR]
[TD]8522[/TD]
[/TR]
[TR]
[TD]8553[/TD]
[/TR]
[TR]
[TD]8553[/TD]
[/TR]
[TR]
[TD]8553[/TD]
[/TR]
[TR]
[TD]8558[/TD]
[/TR]
[TR]
[TD]8562[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

How about this?

=SUMPRODUCT(((LEFT(NRange,1)+0)=8)/COUNTIF(NRange,NRange&""))

Where nRange is your range of data.

Also, this is an array formaula so hit control + shift + enter agter pasting the formula in.

 

[lrobbo314]

Having said that, I think a better solution would be to use a helper column to extract the first digit of each number. So, if your data is in column A, then column B would have the formula = Left(A2,1). Then just put the whole thing into a Pivot Table. A lot more dynamic and useful IMO.

 

[Marcelo Branco]

Try

For testing purposes I included two rows with numbers that don't begin with 8

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Numbers​

[/td][td][/td][td]

Begin with​

[/td][td]

Count Unique​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td="bgcolor:#FFFF00"]

8404​

[/td][td][/td][td]

8​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

8404​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

8404​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td="bgcolor:#FFFF00"]

8449​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td="bgcolor:#FFFF00"]

8450​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td="bgcolor:#FFFF00"]

8522​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td="bgcolor:#FFFF00"]

8553​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

8553​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

8553​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td="bgcolor:#FFFF00"]

8558​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td="bgcolor:#FFFF00"]

8562​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

9404​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

9404​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Criteria in C2

Array formula in D2
=SUM(IF(FREQUENCY(IF(0+LEFT(A2:A14)=C2,A2:A14),A2:A14),1))
Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[pauln93]

Thank you! I had the exact similar but it seem like I missed the +0 part.

 

[Rick Rothstein]

For those who might find it interesting, here is a UDF (user defined function) solution (which can be called from within other VB code if needed)...

[table="width: 500"]
[tr]
	[td]Function UniqueCount(Rng As Range, BeginWith As String) As Long
  Dim V As Variant, Data As Variant
  Data = Rng.Value
  With CreateObject("Scripting.Dictionary")
    For Each V In Data
      If Left(V, Len(BeginWith)) = BeginWith Then .Item(V) = 1
    Next
    UniqueCount = .Count
  End With
End Function[/td]
[/tr]
[/table]

This function takes two arguments... first is the range to look at and the second is the beginning text (one or more characters) to search for.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use UniqueCount just like it was a built-in Excel function. For example,

=UniqueCount(A1:A10,"8")

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Marcelo Branco]

You are welcome.

The 0+ converts the value returned by LEFT (a string) to a number - it's necessary because C2 (criteria) contains a number.

M.

 

[sandy666]

another way is PivotTable with DataModel to count distinct values and Label filter Begins with... 8