Count Unique With one Condition & No ROUNDUP

[Rob]

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!

 

[Ron Coderre]

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)

 

[Ron Coderre]

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)

 

[Bernd P]

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 here:
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

 

[Rob]

Thanks a Ton! This one works perfectly!

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]

I kept getting a #N/A error out of this one. However the previous one wirks
wonderfully.


Thanks Again!

 

[Rob]

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?

 

[Rob]

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