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