Counting Unique Values Using "Starts with" Criteria

  • Thread starter Thread starter Mark T
  • Start date Start date
M

Mark T

Given the list of text values in column A below, how
would I construct a function to count all the unique
values that start with the text "A1001". Using the data
below, I would want a numeric value of 3 to be returned
as their are three unique entried starting with "A1001".

A1000145
A1000087
A1001087
A1001419
A1001419
A1001419
A1001419
A1001610
A1001610
A1002013
A1002012
A1002012
A1002417
A1002421

Thanks for any all help.
 
Hi
this array formula works (a1:a14 is the example range where you have all the
values)
=SUM(IF(LEFT($a$1:$a$14;5)="a1001";1;""))
press ctrl + shift + enter to enter the formula
 
=COUNTIF(A1:A100,"A1001*")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for the suggestion but this solution counts every
entry that starts with "A1001", but that's not what I
need. I am looking for a function that counts every
completely unique values in the range, evaluating all
characters in the cells, that starts with "A1001". The
suggestion only evaluates the first 5 characters for
uniqueness and returns a count of 7 mathcing instances
from the sample data below.

In the sample data there are 4 instances of "A1001419",
which I want counted as 1 unique instance. There are 2
instances of "A1001610", that also should be counted as
1. And there is a single instance of "A1001087" that
should be counted as 1. So the result of the function I
am seeking would be a count of 3. The function I need
would say "give me a count of all unique values that
start with "A1001" but evaluates the entire set of
characters in the cells for uniqeness, not just the first
5. Hope that makes sense. Let me know if you are aware of
anything that would do this for me.
 
Thanks for the suggestion but this solution counts every
entry that starts with "A1001", but that's not what I
need. I am looking for a function that counts every
completely unique values in the range, evaluating all
characters in the cells, that starts with "A1001". The
suggestion only evaluates the first 5 characters for
uniqueness and returns a count of 7 mathcing instances
from the sample data below.

In the sample data there are 4 instances of "A1001419",
which I want counted as 1 unique instance. There are 2
instances of "A1001610", that also should be counted as
1. And there is a single instance of "A1001087" that
should be counted as 1. So the result of the function I
am seeking would be a count of 3. The function I need
would say "give me a count of all unique values that
start with "A1001" but evaluates the entire set of
characters in the cells for uniqeness, not just the first
5. Hope that makes sense. Let me know if you are aware of
anything that would do this for me.
 
Thanks for the suggestion but this solution counts every
entry that starts with "A1001", but that's not what I
need. I am looking for a function that counts every
completely unique values in the range, evaluating all
characters in the cells, that starts with "A1001". The
suggestion only evaluates the first 5 characters for
uniqueness and returns a count of 7 mathcing instances
from the sample data below.

In the sample data there are 4 instances of "A1001419",
which I want counted as 1 unique instance. There are 2
instances of "A1001610", that also should be counted as
1. And there is a single instance of "A1001087" that
should be counted as 1. So the result of the function I
am seeking would be a count of 3. The function I need
would say "give me a count of all unique values that
start with "A1001" but evaluates the entire set of
characters in the cells for uniqeness, not just the first
5. Hope that makes sense. Let me know if you are aware of
anything that would do this for me.
 
One way to do this would be to use data>advanced filter, assume your data
starts in A2 with a header and
the values in A3. In an empty cell in the first row put this formula

=SUBTOTAL(3;A3,A200)

replace the range with your range..

now put this formula in row 2

=LEFT(A3,5)="A1001"

do data>filter>advanced filter

in the list range put

$A$2:$A$200

in the criteria range put

$D$1:$D$2

where D1 is an empty cell and D2 holds the formula =LEFT(A3,5)="A1001"

now select unique records only and click OK..

the subtotal formula should now return 3 according to the conditions you set

--

Regards,

Peo Sjoblom
 
That formula does exactly what you require.

Thanks for the suggestion but this solution counts every
entry that starts with "A1001", but that's not what I
need. I am looking for a function that counts every
completely unique values in the range, evaluating all
characters in the cells, that starts with "A1001". The
suggestion only evaluates the first 5 characters for
uniqueness and returns a count of 7 mathcing instances
from the sample data below.

In the sample data there are 4 instances of "A1001419",
which I want counted as 1 unique instance. There are 2
instances of "A1001610", that also should be counted as
1. And there is a single instance of "A1001087" that
should be counted as 1. So the result of the function I
am seeking would be a count of 3. The function I need
would say "give me a count of all unique values that
start with "A1001" but evaluates the entire set of
characters in the cells for uniqeness, not just the first
5. Hope that makes sense. Let me know if you are aware of
anything that would do this for me.
Click to expand...
 
This is good. One question though. If I use the
data>filter>advanced filter menu option, do I have to
manually go into that menu option each time to have the
result recalculated when the data changes? I've never
used that menu option but the solution I'm after must be
able to get my desired result by setting it up once and
having it recaculate automaticlly when the data changes
just like a function. Let me know. Thanks again!
 
No but you could have a macro that would do that but if you want a formula
go with Aladin's
use of Laurent Langre's add-in
 
Mark

Here's a solution with an array formula:

=SUM((FREQUENCY(IF(ISERROR(VALUE(SUBSTITUTE($A$2:$A$15,"A1001",""))),
FALSE,VALUE(SUBSTITUTE($A$2:$A$15,"A1001",""))),
IF(ISERROR(VALUE(SUBSTITUTE($A$2:$A$15,"A1001",""))),FALSE,
VALUE(SUBSTITUTE($A$2:$A$15,"A1001",""))))>0)+0)

The formula must be entered with <Shift><Ctrl><Enter>, also if edited later.
If done correctly, Excel will display the formula in the formula bar
enclosed in curly brackets { } Don't enter these brackets yourself.

The formula is not a general one. In order to work, at least one character
in the searchstring (here "A1001") must be non-numeric, and the rest of the
string must contain numeric characters only.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Mark T said:
Given the list of text values in column A below, how
would I construct a function to count all the unique
values that start with the text "A1001". Using the data
below, I would want a numeric value of 3 to be returned
as their are three unique entried starting with "A1001".

A1000145
A1000087
A1001087
A1001419
A1001419
A1001419
A1001419
A1001610
A1001610
A1002013
A1002012
A1002012
A1002417
A1002421
Click to expand...

If the list above were in A1:A14, you could use

=SUMPRODUCT((LEFT(A1:A14,5)="A1001")
/(COUNTIF(A1:A14,A1:A14)+(LEFT(A1:A14,5)<>"A1001")))
 
Neat way of getting the fractions!

LeoH


Harlan Grove said:
If the list above were in A1:A14, you could use

=SUMPRODUCT((LEFT(A1:A14,5)="A1001")
/(COUNTIF(A1:A14,A1:A14)+(LEFT(A1:A14,5)<>"A1001")))
Click to expand...
 
Harlan,

Can you please explain to me why you added the:
+(LEFT(A1:A14,5)<>"A1001")
in your formula.

I thought that it might be to prevent errors when there are no matches, but
a formula without the above addition works just as well. I also tried when
all the data matched the "A1001" and when all the data is exactly the same
entries but again it works without the addition. I assume that it is needed
to take care of some scenario that I have not thought of but what is it?

Thank you,

Sandy
 
Can you please explain to me why you added the:
+(LEFT(A1:A14,5)<>"A1001")
in your formula.

I thought that it might be to prevent errors when there are no matches, but ...
Click to expand...

It's not needed to catch no matches, it's needed when there may be blank cells
in the range being processed (for which COUNTIF returns 0, risking a #DIV/0!
error). But it could be simplified to

=SUMPRODUCT((LEFT(A1:A14,5)="A1001")/(COUNTIF(A1:A14,A1:A14)+ISBLANK(A1:A14)))
 
..
It's not needed to catch no matches, it's needed when there may be blank cells
in the range being processed (for which COUNTIF returns 0, risking a #DIV/0!
error). But it could be simplified to

=SUMPRODUCT((LEFT(A1:A14,5)="A1001")/(COUNTIF(A1:A14,A1:A14)+ISBLANK(A1:A14)
))
Click to expand...

Thank you Harlan,

I never thought about blank cells but I knew that you had to have a reason
for it.

Sandy
 
Back
Top