Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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))) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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"????? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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:) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
Sample file sent...
In article , ajajmannen My e-mail is: ...... |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 :) |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the number of unique Numbers in a column
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totaling the number of comma delimited numbers in a column | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Count number of items in one column that have a value in another? | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) |