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.


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

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

"ajajmannen" wrote:


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.


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

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!

In article ,
ajajmannen
wrote:

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.

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
0<"",A1:A3000)),IF(ISNUMBER(MATCH(N1:N3000,{"SE", "INC"},0)),IF(A1:A3000<
"",A1:A3000))))

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

Hope this helps!

In article ,
ajajmannen
wrote:

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.

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(A 1: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:A300 0)))


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

Try...

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

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

Hope this helps!

In article ,
ajajmannen
wrote:

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(A 1: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:N2 385,);0;IF(A1:A3000<"";A1:A
3000)))

Domenic Wrote:
Try...

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

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

Hope this helps!

In article ,
ajajmannen
wrote:

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(A 1: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:N2 385,);0;IF(A1:A3000<"";A1:A
3000)))

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"?????


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

Try the following formula instead...

=COUNT(1/FREQUENCY(IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N300 0;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!

In article ,
ajajmannen
wrote:

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 Wrote:
Try the following formula instead...

=COUNT(1/FREQUENCY(IF((LEFT(N1:N3000;2)="SE")+(LEFT(N1:N300 0;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!

In article ,
ajajmannen
wrote:

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"?????


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.


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

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

=ISNUMBER(A2)

In article ,
ajajmannen
wrote:

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 Wrote:
One possibility is that the numbers in Column A are being recognized as
text. What do you get when you try...

=ISNUMBER(A2)

In article ,
ajajmannen
wrote:

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.

You were correct there It gave a false value but I changed that and got
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 number
like 1937451. The Value in the N Column can look like this: SE96745 or
like INC-095667.
The numbers in Column A can appear several times and i only want to
count how many unique hits i get.
The values in column N can also be NOXXXXX and DKXXXXX but I only want
to count the unique numbers in Column A that meet the condition SE* or
INC*.
I would like to get.

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


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

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?

In article ,
ajajmannen
wrote:

You were correct there It gave a false value but I changed that and got
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 number
like 1937451. The Value in the N Column can look like this: SE96745 or
like INC-095667.
The numbers in Column A can appear several times and i only want to
count how many unique hits i get.
The values in column N can also be NOXXXXX and DKXXXXX but I only want
to count the unique numbers in Column A that meet the condition SE* or
INC*.
I would like to get.

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

Domenic Wrote:
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?

In article ,
ajajmannen
wrote:

You were correct there It gave a false value but I changed that and
got
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
number
like 1937451. The Value in the N Column can look like this: SE96745
or
like INC-095667.
The numbers in Column A can appear several times and i only want to
count how many unique hits i get.
The values in column N can also be NOXXXXX and DKXXXXX but I only
want
to count the unique numbers in Column A that meet the condition SE*
or
INC*.
I would like to get.

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

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.


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

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

In article ,
ajajmannen
wrote:

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 Wrote:
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...

In article ,
ajajmannen
wrote:

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.

My e-mail is:


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

Sample file sent...

In article ,
ajajmannen

My e-mail is: ......

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
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

ajajmannen Wrote:
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((LE FT(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+****+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 :)


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

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(Shee t1!N2:N2385;3)="INC");I
F(Sheet1!T2:T2385="ABD";IF(Sheet1!A2:A2385<"";She et1!A2:A2385)))))

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

Hope this helps!

In article ,
ajajmannen
wrote:

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:A23 85<"";Sheet1!A2:A2385));IF(
(LEFT(Sheet1!N2:N2385;2)="SE")+(LEFT(Sheet1!N2:N23 85;3)="INC");AND(Sheet1!T2:T
2385="ABD");IF(Sheet1!A2:A2385<"";Sheet1!A2:A2385 ))))
I have tried to use CTRL+****+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 :)

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

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


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=538999

It's because the AND function only returns a single value, not an
array....

Hope this helps!

In article ,
ajajmannen
wrote:

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

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