Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

sconti11

Technical User
Jan 31, 2011
95
US
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?
 


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

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

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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?
 



PivotTable!

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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!
 



It can! Look again.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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?
 



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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 


This can be done with MS Query. It would be a query within a query. Like...
Code:
select months, [server name], count(*)
from (
select distinct months, [server name]
from [Servers$]
)
group by months, [server name]
faq68-5829

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


This may be what you want...
Code:
select months, count(*)
from (
select distinct months, [server name]
from [Servers$]
)
group by months

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top