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]

hi,

What version of Excel?

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

 

[sconti11]

2007

 

[SkipVought]

Have you looked at COUNTIF[red]S[/red]()

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

 

[sconti11]

I see how the COUNTIFS can be useful for my multiple conditions, but I am still strugling with the counting of unique values only??

 

[SkipVought]

something like this where PrtNbr is a named range.
[tt]
=SUM(IF(FREQUENCY(MATCH(PrtNbr,PrtNbr,0),MATCH(PrtNbr,PrtNbr,0))>0,1))
[/tt]
In your case, you would need a helper column that concatenates your two values.

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

 

[sconti11]

I do not understand what you mean by a helper column??

Here is what I have right now, but DOES NOT remove duplicates:

=COUNTIF(Workbooks!$C$4:$C$1998,"Jan")

All that this does above is count where the range has "Jan", but as I stated I need to test a different range for unique values to get an accurate count.

I was thinking if I could use an IF statement to test for the unique, and then if true, use the above COUNTIF???

Additional help would be great!

 

[SkipVought]

A helper column is a column that you add to help you get to a solution.

So assuming that the next empty column is C, your helper column formula would be...
[tt]
C2: =A2&B2
[/tt]
Then your would use the SUM(IF(FREQUENCY(MATCH... formula on that column of data.

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

 

[sconti11]

ok...i see what you mean, but that is now how my data is presented. I probably need to provide more details to my workbook.

I have a data connection to an access database to retrieve data and present it in the workbook. I then have a seperate tab which is used to count the values within the returned data.

So ColA has months, ColB has text data, ColC has numeric data,...and so on.

So based on what you stated, there is not an empty column next to the column I am testing..

 

[SkipVought]

So assuming that the [red]next[/red] empty column is C...

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

 

[sconti11]

Here is what did, based on your suggestion. I applied the helper column to column "P" and then i added the following formula:

=SUM(IF(FREQUENCY(MATCH(Servers!P32000,Servers!P32000,0),MATCH(Servers!P32000,Servers!P32000,0))>0,1))

But I am getting "N/A"??

Do you see where I am going wrong?

 

[SkipVought]

I used the formula you posted and got a valid result!

Are you sure that your column P P32000 range, contains no errors.

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

 

[sconti11]

No errors are found in that column...

 

[SkipVought]

what cell is your formula in?

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

 

[sconti11]

I placed the formula on the first tab, which is my summary tab.

That is why the formula refers to the tab "Servers"...to be exact I have the formula in cell D20 on my Summary tab worksheet.

 

[SkipVought]

This is why I use a Named Range, which contains DATA and not ENPTY CELLS.

Limit your range to the range that contains DATA.

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

 

[sconti11]

ok...I was able to get a count..but this leads me to two questions:

1. How to then incorporate the additional IF statement to test for the month in ColA. (i.e. COUNTIF(A3:A400,"Jan"))

2. How to dynamically expand the range for my named range ("PrtNbr"), because I am bringing in data from Access, and the rows will increase.

Thanks alot for all your help!

 

[sconti11]

Here is what I came up with...yet it is still counting the duplicate records:

=IF(FREQUENCY(MATCH(PrtNbr,PrtNbr,0),MATCH(PrtNbr,PrtNbr,0))>0,COUNTIF(Servers!C3:C500,"Jan"))

I am not familiar with the FREQUENCY or MATCH function,,,so any assistance will be helpful!

 

[SkipVought]

Excel HELP helps.

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

 

[sconti11]

Ok...here is what I came up with, and it seems to work when I am not employing a dynamic Name Range:

=SUM(COUNTIF(Servers!C3:C500,"Jan")) - IF(FREQUENCY(MATCH(PrtNbr,PrtNbr,0),MATCH(PrtNbr,PrtNbr,0))>0,1)

But I need the range "PrtNbr" to be dynamic since I am populating the data from an access database daily.

I have tried using the following to create a dynamic range:

=OFFSET(Servers!$P$3,0,0,COUNTA(Servers!$P:$P),1)

But all i get within the the worksheet that has the count formula is N/A.

Please assist!

Thanks