Count the number of unique Numbers in a column

[ajajmannen]

Hi!!

i'm currently trying to count the number of unique numbers in a Column
using this formula:
=SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&""))
So far so good.....But I want to add some conditions like only count
the numbers that meet the condition say N1:N3000="SE*"

I tried a couple of things but nothing seem to work can you please help
out.....Don't laugh and I will paste my own failed solutions:
=SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&""))))
On this one I get #VALUE! and i can't figure out why.
I have also tried to put the AND/OR condition before the actual
starting of the array of the SUMPRODUCT but nothing.

 

[Guest]

Try .....

=SUMPRODUCT(--(LEFT(N3:N3000,3)="INC"),(A3:A3000<>"")/COUNTIF(A3:A3000,A3:A3000&""))
SUMPRODUCT(--(LEFT(N3:N3000,2)="SE"),(A3:A3000<>"")/COUNTIF(A3:A3000,A3:A3000&""))

I don't think you can OR with SUMPRODUCT (but I could be wrong!)

HTH

 

[Domenic]

Try...

=COUNT(1/FREQUENCY(IF(LEFT(N1:N3000,2)="SE",IF(A1:A3000<>"",A1:A3000)),IF
(LEFT(N1:N3000,2)="SE",IF(A1:A3000<>"",A1:A3000))))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Domenic]

I just noticed the second part of the question. To count the number of
unique numbers in Column A where the corresponding value in Column N is
either SE or INC, try...

=COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH(N1:N3000,{"SE","INC"},0)),IF(A1:A300

"",A1:A3000))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[ajajmannen]

Hi!!

I tried your formula but excel is complaining about the Lookup value in
the first Match formula?

When I look at the help it states that the Value I'm looking for should
be second and the Array second but I don't seem to get it right
anyway......

Please help me here
First of all I probably have a diffrent version of excel I use smicolon
instead of colon.
I tried something like this but now it stops at last parentes after the
first IF statement


=COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH({"SE";"INC"};(N1:N2385);0;IF(A1:A3000<>"";A1:A3000)))
IT stops here and I don't know why??
;IF(ISNUMBER(MATCH({"SE","INC"};(N1:N2385,);0;IF(A1:A3000<>"";A1:A3000)))

=COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH({"SE";"INC"};(N1:N2385);0;IF(A1:A3000<>"";A1:A3000)));IF(ISNUMBER(MATCH({"SE","INC"};(N1:N2385,);0;IF(A1:A3000<>"";A1:A3000)))

 

[Domenic]

Try...

=COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH(N1:N3000,{"SE";"INC"};0));IF(A1:A300

"";A1:A3000))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[ajajmannen]

Try...

=COUNT(1/FREQUENCY(IF(ISNUMBER(MATCH(N1:N3000,{"SE";"INC"};0));IF(A1:A300


....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Ok one step closer.....It counted 0......I think i know why.....I would
like to use a wildcard on the SE and INC....As all of the values in that
column either starts with SE or INC so a value can look like INC96835 or
INC99784....Is there a way to get it to match the value with either a
wildcard or by stating "starts with"?????

 

[Domenic]

Try the following formula instead...

=COUNT(1/FREQUENCY(IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N3000;3)="INC");IF
(A1:A3000<>"";A1:A3000));IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N3000;3)="IN
C");IF(A1:A3000<>"";A1:A3000))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[ajajmannen]

Try the following formula instead...

=COUNT(1/FREQUENCY(IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N3000;3)="INC");IF
(A1:A3000<>"";A1:A3000));IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N3000;3)="IN
C");IF(A1:A3000<>"";A1:A3000))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

I must say many thanks for all the help.....But It still counts it to 0
and I know that it should be around 700 entries at least.

Please help out.....I don't know what more information to give to be
able to get the correct formula.

 

[Domenic]

One possibility is that the numbers in Column A are being recognized as
text. What do you get when you try...

=ISNUMBER(A2)

 

[ajajmannen]

One possibility is that the numbers in Column A are being recognized as
text. What do you get when you try...

=ISNUMBER(A2)

You were correct there It gave a false value but I changed that and go
a TRUE value......but it is still giving the result 0.
Ok to give more information: The vaules in Column A is a 7 digit numbe
like 1937451. The Value in the N Column can look like this: SE96745 o
like INC-095667.
The numbers in Column A can appear several times and i only want t
count how many unique hits i get.
The values in column N can also be NOXXXXX and DKXXXXX but I only wan
to count the unique numbers in Column A that meet the condition SE* o
INC*.
I would like to get.

This formula is really getting on my nerv......And again I want t
thank you for helping out and I hope that we will find the answer:

 

[Domenic]

Then it's likely that the numbers for the remainder of the column are
also being recognized as text. Try the following...

1) Select an empty cell

2) Edit > Copy

3) Select Column A

4) Edit > Paste Special > Add > Ok

Does this help?

 

[ajajmannen]

Then it's likely that the numbers for the remainder of the column are
also being recognized as text. Try the following...

1) Select an empty cell

2) Edit > Copy

3) Select Column A

4) Edit > Paste Special > Add > Ok

Does this help?

Tried that to...but it seem like the problem is elsewhere...
I will copy a sample of the to columns in here and maybe you can try
the formula in action.
My Version of excel i 2002 with sp3.
Column N
USER_ID
SE096932
SE096932
SE096932
SE096932
SE096932
INC-97173
INC-97173
SE096932
SE096932

Column A
PROBLEM_ID
1499986
1499986
1499986
1499986
1758434
1929352
1929352
1936837
1936837
If this formula would work then the result for the sample would be 4
Hopefully this will make it easier for you
And just for the record....I really appreciate the help.

 

[Domenic]

Based on the data supplied, the formula does indeed return 4. If you'd
like, I can email you a sample file. If so, send me your email
address...

 

[ajajmannen]

Based on the data supplied, the formula does indeed return 4. If you'd
like, I can email you a sample file. If so, send me your email
address...



Column A

My e-mail is: (e-mail address removed)

 

[Domenic]

Sample file sent...

 

[ajajmannen]

Thanks alot it was really a great help....

What I don't understand is why the formula didn't work before you
posted it in a Sample file.....I really did CTRL, SHIFT + ENTER.

But it doesn't matter it do work now

This will be a big help for me as this reports needs to be analyzed
every month.

 

[ajajmannen]

Thanks alot it was really a great help....

What I don't understand is why the formula didn't work before you
posted it in a Sample file.....I really did CTRL, SHIFT + ENTER.

But it doesn't matter it do work now

This will be a big help for me as this reports needs to be analyzed
every month.

Would you find me to annoying if I asked a followup question regarding
the formula.
I'm now trying to add a AND condition to the IF statement. But it
doesn't work.
the formula looks like this:
=COUNT(1/FREQUENCY(IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N2385;3)="INC");AND(Sheet1!T2:T2385="ABD");IF(Sheet1!A2:A2385<>"";Sheet1!A2:A2385));IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N2385;3)="INC");AND(Sheet1!T2:T2385="ABD");IF(Sheet1!A2:A2385<>"";Sheet1!A2:A2385))))
I have tried to use CTRL+SHIT+ENTER but it only counts the same number
even thou I change the second AND logical condition to another value.
If it isn't to much to ask please help me find out what the problem
is?

This FORUM is really the best ever for that matter

 

[Domenic]

No problem at all. Here you go...

=COUNT(1/FREQUENCY(IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N238
5;3)="INC");IF(Sheet1!T2:T2385="ABD";IF(Sheet1!A2:A2385<>"";Sheet1!A2:A23
85)));IF((LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N2385;3)="INC");I
F(Sheet1!T2:T2385="ABD";IF(Sheet1!A2:A2385<>"";Sheet1!A2:A2385)))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[ajajmannen]

Thanks again .....One question thou.....why doesn't it work with the AN
condition after the IF statement...I have several other forumlas where
have that?

Is it because the COUNT or frequency fromula can't combine AND