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