Count Distinct Values in Excel With COUNTIF?????

[sconti11]

I am in need of counting distinct text values within a column where the 2nd column is equal to a value.

Example:
ColA has values such as Jan, Feb, Mar, etc
ColB has values such as FGG1212, GGF2233, etc

ColB will have repeating values. I need to count ColB where ColA equals "Jan", but they have to be unique values.

Can someone assist me with this?

 

[SkipVought]

This can give you the stats you want, I believe:

Insert a PivotTable using ColA & ColB (forget the helper column)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sconti11]

Forget that...my calculation will NOT work!

The piece of the formula that should check for duplicates (IF(FREQUENCY(MATCH(PrtNbr,PrtNbr,0),MATCH(PrtNbr,PrtNbr,0))>0,1)) is not correct.

If I apply the sum like this:

SUM(IF(FREQUENCY(MATCH(PrtNbr,PrtNbr,0),MATCH(PrtNbr,PrtNbr,0))>0,1)) then I get a total count of duplicates, whereas I am ONLY looking for duplicates that are part of the first condition, that being where Servers!$C$3:$C$500 = "Jan".

Do u see my issue?

 

[SkipVought]

PivotTable!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sconti11]

Yea...I tried the pivot table...but that alone will not give me the unique counts I am looking for.

Thanks for your patience with me!

 

[SkipVought]

It can! Look again.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sconti11]

Yea...I am looking...and unlike you I do not have "Programmer Eyes".

I inserted a pivot table using ColA (Months) and ColB (Server Names), and then I used ColA as the Column fields and ColB I moved to the Values so as to get a count.

It counts the instances, but does not remove duplicates. So just using ONLY the pivot table will not suffice. I am scowering the Web for other ways to do this. But it sounds like you know exactly what I need to do. Can you give me a little more insight?

 

[SkipVought]

You do not use the COUNTS in the PT!

Rather you COUNTA the occurrences in the ServerNames as you SELECT the MONTHS value in a FILTER, naturally subtracting the extraneous static value count.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sconti11]

Yea...this sounds very manual. I get what you mean, but I guess i am looking to automate this more efficiently.

So the pivot table does provide me a unique list of servers per month, but now I need to create a formula that will count those and place them in my first tab which is the summary tab.

I think this got more complicated...since I still need to resolve the dynamic range too.

Well...I will keep searching!

Thanks

 

[SkipVought]

This can be done with MS Query. It would be a query within a query. Like...

select months, [server name], count(*)
from (
select distinct months, [server name]
from [Servers$]
)
group by months, [server name]

faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

This may be what you want...

select months, count(*)
from (
select distinct months, [server name]
from [Servers$]
)
group by months

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]