Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique With one Condition & No ROUNDUP
Hello.... I've looked through a lot of posts here and unfortunately I cannot
find a formula that helps in my issue. What I am looking to do is within an Excel 2003 worksheet I have several columns and I need to count all the unique values of Column "A" that also have a criteria of Column "K" being either; "In Work", "Completed", or "Not Started". I found many formulae that sort of did what I am looking for but they involved performing division and the end results keep coming up as a non-whole number, e.g. '14.12'. In order to combat this issue I have used a "Roundup" with the formula however this does not fully operate correctly when low decimals are continuous. For example... If I start off with '14.01' = ~15 and I add one more unique value the result is '14.45' = ~15.... but it should now be 16. :( Here is the formula I am working with that best suited what I am trying to do, (roundup added). =ROUNDUP(SUMPRODUCT((K2:K100="In Work")/COUNTIF(A2:A100,A2:A100&"")*(A2:A100<"")),0) If I can count all the "Unique" Column "A" items that happen to also be Column "K" - "In Work" without having to roundup I would feel that the data is actually accurate. Any help would be outstanding. Thanks In Advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique With one Condition & No ROUNDUP
Using your posted example formula as a guide
With K2:K100 containing status....eg Not Started and A2:A100 containing some other value for that record AND... D1 containing the status you want a unique Col_A count for eg Completed This formula returns the unique count of Col_A values where the corresponding Col_K value equals D1 B1: =SUMPRODUCT(--(FREQUENCY((K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0),(K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0))*(K2:K101=D1)0)) Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "Rob" wrote in message ... Hello.... I've looked through a lot of posts here and unfortunately I cannot find a formula that helps in my issue. What I am looking to do is within an Excel 2003 worksheet I have several columns and I need to count all the unique values of Column "A" that also have a criteria of Column "K" being either; "In Work", "Completed", or "Not Started". I found many formulae that sort of did what I am looking for but they involved performing division and the end results keep coming up as a non-whole number, e.g. '14.12'. In order to combat this issue I have used a "Roundup" with the formula however this does not fully operate correctly when low decimals are continuous. For example... If I start off with '14.01' = ~15 and I add one more unique value the result is '14.45' = ~15.... but it should now be 16. :( Here is the formula I am working with that best suited what I am trying to do, (roundup added). =ROUNDUP(SUMPRODUCT((K2:K100="In Work")/COUNTIF(A2:A100,A2:A100&"")*(A2:A100<"")),0) If I can count all the "Unique" Column "A" items that happen to also be Column "K" - "In Work" without having to roundup I would feel that the data is actually accurate. Any help would be outstanding. Thanks In Advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique With one Condition & No ROUNDUP
A bit shorter and fewer "moving parts":
B1: =SUMPRODUCT(--(FREQUENCY( MATCH(A2:A100&K2:K100,A2:A100&K2:K100,0), MATCH(A2:A100&K2:K100,A2:A100&K2:K100,0))* (K2:K101=D1)0)) Regards, Ron Coderre Microsoft MVP (Excel) "Ron Coderre" wrote in message ... Using your posted example formula as a guide With K2:K100 containing status....eg Not Started and A2:A100 containing some other value for that record AND... D1 containing the status you want a unique Col_A count for eg Completed This formula returns the unique count of Col_A values where the corresponding Col_K value equals D1 B1: =SUMPRODUCT(--(FREQUENCY((K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0),(K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0))*(K2:K101=D1)0)) Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "Rob" wrote in message ... Hello.... I've looked through a lot of posts here and unfortunately I cannot find a formula that helps in my issue. What I am looking to do is within an Excel 2003 worksheet I have several columns and I need to count all the unique values of Column "A" that also have a criteria of Column "K" being either; "In Work", "Completed", or "Not Started". I found many formulae that sort of did what I am looking for but they involved performing division and the end results keep coming up as a non-whole number, e.g. '14.12'. In order to combat this issue I have used a "Roundup" with the formula however this does not fully operate correctly when low decimals are continuous. For example... If I start off with '14.01' = ~15 and I add one more unique value the result is '14.45' = ~15.... but it should now be 16. :( Here is the formula I am working with that best suited what I am trying to do, (roundup added). =ROUNDUP(SUMPRODUCT((K2:K100="In Work")/COUNTIF(A2:A100,A2:A100&"")*(A2:A100<"")),0) If I can count all the "Unique" Column "A" items that happen to also be Column "K" - "In Work" without having to roundup I would feel that the data is actually accurate. Any help would be outstanding. Thanks In Advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique With one Condition & No ROUNDUP
Hello,
I suggest to select a sufficiently long area with 2 columns and array- enter: =Pfreq(Pfreq(K1:K10,A1:A10)) Or, if the rounded-up values should be unique: =Pfreq(Pfreq(K1:K10,ROUNDUP(A1:A10,0))) Pfreq you can find he http://www.sulprobil.com/html/pfreq.html If the double-call of Pfreq irritates you, call it once first (but take 3 columns then) and apply the second call to the output area of the first call... Regards, Bernd PS: 1. Array-enter means CTRL + SHIFT + ENTER, not only ENTER 2. You can use my UDF as follows: a) Press ALT + F11 b) Insert a (macro-) module c) Copy the macro code from my website into this module d) Go back to your worksheet and use my code as described |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique With one Condition & No ROUNDUP
Thanks a Ton! This one works perfectly!
"Ron Coderre" wrote: Using your posted example formula as a guide With K2:K100 containing status....eg Not Started and A2:A100 containing some other value for that record AND... D1 containing the status you want a unique Col_A count for eg Completed This formula returns the unique count of Col_A values where the corresponding Col_K value equals D1 B1: =SUMPRODUCT(--(FREQUENCY((K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0),(K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0))*(K2:K101=D1)0)) Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "Rob" wrote in message ... Hello.... I've looked through a lot of posts here and unfortunately I cannot find a formula that helps in my issue. What I am looking to do is within an Excel 2003 worksheet I have several columns and I need to count all the unique values of Column "A" that also have a criteria of Column "K" being either; "In Work", "Completed", or "Not Started". I found many formulae that sort of did what I am looking for but they involved performing division and the end results keep coming up as a non-whole number, e.g. '14.12'. In order to combat this issue I have used a "Roundup" with the formula however this does not fully operate correctly when low decimals are continuous. For example... If I start off with '14.01' = ~15 and I add one more unique value the result is '14.45' = ~15.... but it should now be 16. :( Here is the formula I am working with that best suited what I am trying to do, (roundup added). =ROUNDUP(SUMPRODUCT((K2:K100="In Work")/COUNTIF(A2:A100,A2:A100&"")*(A2:A100<"")),0) If I can count all the "Unique" Column "A" items that happen to also be Column "K" - "In Work" without having to roundup I would feel that the data is actually accurate. Any help would be outstanding. Thanks In Advance! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique With one Condition & No ROUNDUP
I kept getting a #N/A error out of this one. However the previous one wirks
wonderfully. Thanks Again! "Ron Coderre" wrote: A bit shorter and fewer "moving parts": B1: =SUMPRODUCT(--(FREQUENCY( MATCH(A2:A100&K2:K100,A2:A100&K2:K100,0), MATCH(A2:A100&K2:K100,A2:A100&K2:K100,0))* (K2:K101=D1)0)) Regards, Ron Coderre Microsoft MVP (Excel) "Ron Coderre" wrote in message ... Using your posted example formula as a guide With K2:K100 containing status....eg Not Started and A2:A100 containing some other value for that record AND... D1 containing the status you want a unique Col_A count for eg Completed This formula returns the unique count of Col_A values where the corresponding Col_K value equals D1 B1: =SUMPRODUCT(--(FREQUENCY((K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0),(K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0))*(K2:K101=D1)0)) Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "Rob" wrote in message ... Hello.... I've looked through a lot of posts here and unfortunately I cannot find a formula that helps in my issue. What I am looking to do is within an Excel 2003 worksheet I have several columns and I need to count all the unique values of Column "A" that also have a criteria of Column "K" being either; "In Work", "Completed", or "Not Started". I found many formulae that sort of did what I am looking for but they involved performing division and the end results keep coming up as a non-whole number, e.g. '14.12'. In order to combat this issue I have used a "Roundup" with the formula however this does not fully operate correctly when low decimals are continuous. For example... If I start off with '14.01' = ~15 and I add one more unique value the result is '14.45' = ~15.... but it should now be 16. :( Here is the formula I am working with that best suited what I am trying to do, (roundup added). =ROUNDUP(SUMPRODUCT((K2:K100="In Work")/COUNTIF(A2:A100,A2:A100&"")*(A2:A100<"")),0) If I can count all the "Unique" Column "A" items that happen to also be Column "K" - "In Work" without having to roundup I would feel that the data is actually accurate. Any help would be outstanding. Thanks In Advance! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique With one Condition & No ROUNDUP
Interesting Item and solution however if other users do not have that
particular file installed onto their PCs would that not mean that they wouldn't be able to utilize the results of that function/code? "Bernd P" wrote: Hello, I suggest to select a sufficiently long area with 2 columns and array- enter: =Pfreq(Pfreq(K1:K10,A1:A10)) Or, if the rounded-up values should be unique: =Pfreq(Pfreq(K1:K10,ROUNDUP(A1:A10,0))) Pfreq you can find he http://www.sulprobil.com/html/pfreq.html If the double-call of Pfreq irritates you, call it once first (but take 3 columns then) and apply the second call to the output area of the first call... Regards, Bernd PS: 1. Array-enter means CTRL + SHIFT + ENTER, not only ENTER 2. You can use my UDF as follows: a) Press ALT + F11 b) Insert a (macro-) module c) Copy the macro code from my website into this module d) Go back to your worksheet and use my code as described |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique With one Condition & No ROUNDUP
My previous post/reply does not seem to be showing up but I just wanted to
ensure that I reply saying that This formula that you offered works perfectly and is an awesome solution! Thanks A Ton for your Help!!! "Ron Coderre" wrote: Using your posted example formula as a guide With K2:K100 containing status....eg Not Started and A2:A100 containing some other value for that record AND... D1 containing the status you want a unique Col_A count for eg Completed This formula returns the unique count of Col_A values where the corresponding Col_K value equals D1 B1: =SUMPRODUCT(--(FREQUENCY((K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0),(K2:K100=D1)* MATCH(A2:A100&"_",A2:A100&"_",0))*(K2:K101=D1)0)) Does that help? Regards, Ron Coderre Microsoft MVP (Excel) "Rob" wrote in message ... Hello.... I've looked through a lot of posts here and unfortunately I cannot find a formula that helps in my issue. What I am looking to do is within an Excel 2003 worksheet I have several columns and I need to count all the unique values of Column "A" that also have a criteria of Column "K" being either; "In Work", "Completed", or "Not Started". I found many formulae that sort of did what I am looking for but they involved performing division and the end results keep coming up as a non-whole number, e.g. '14.12'. In order to combat this issue I have used a "Roundup" with the formula however this does not fully operate correctly when low decimals are continuous. For example... If I start off with '14.01' = ~15 and I add one more unique value the result is '14.45' = ~15.... but it should now be 16. :( Here is the formula I am working with that best suited what I am trying to do, (roundup added). =ROUNDUP(SUMPRODUCT((K2:K100="In Work")/COUNTIF(A2:A100,A2:A100&"")*(A2:A100<"")),0) If I can count all the "Unique" Column "A" items that happen to also be Column "K" - "In Work" without having to roundup I would feel that the data is actually accurate. Any help would be outstanding. Thanks In Advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique with criteria condition | Excel Worksheet Functions | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
how to count unique entries with multiple condition | Excel Worksheet Functions | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions | |||
Count of unique items meeting condition | Excel Worksheet Functions |