COUNTIF (unique values only)

[vijyat]

Hi Guys,

I was wondering if someone can help me out. I have an excel sheet (see attached) that I am trying to find the total count excluding the duplicates. I read different forums and it mentioned something about a COUNTDIFF formula, which is a separate addon. But I need to share the workbook so I can't really use that. Also I am looking more for formulas rather than VBA. :banghead:

In the excel sheet, there are 3 categories that I need to calculate the total among 4 different regions and 2 countries. I use the formula "=COUNTIF(range, criteria)" however, it accounts for duplicates. How can I modify it to add MATCH or some other function such that it avoids duplicates(unique values only) and still gives me a total count.

Need help please.

Thanks,
Vijyat

[Aussiebear]

Try the following;


=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)) ' change the ranges to suit your worksheet

[vijyat]

Hi Aussibear,

thanx for the prompt reply. I tested it by changing the ranges to"B3:I24" (selecting data in both AMERICA & CANADA for all 4 regions) to select the entire data but it just comes to "0". I also tried just the range within 4 regions in AMERICA and it still comes to "0". Not sure what I am doing wrong.

thnx

[Aussiebear]

Okay, then try this;


=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1)) ' Change to suit your range

[vijyat]

yup tried that aswell.This time it's giving the #N/A error. So If I am choosing the entire range, how does it compute whether it needs to calculate F or V or S ? Won't it get the same answer for all of them since I am not specifying a character to MATCH with, instead I am matching a full range with the range itself.

[Tom Jones]

Hi,

try :

=COUNTIF(B3:I24;"F")
=COUNTIF(B3:I24,"V")
=COUNTIF(B3:I24,"S")

[vijyat]

Hey Tom,

Thanks for the reply. The formula you mentioned is exactly what I was using earlier. however, it doesn't exlcude duplicates. If you look into the excel sheet and I need to calculate the total number of fruits I would use =COUNTIF(B3:I24;"F") , but then again it will take into account all the apples as such. Whereas I only need the Fruit(F)-> apples to be counted as one and not more than one. So basically I am looking to count unique values only for each of the category Fruits(F), Vegetables(V) & Spices(S) in the entire range B3:I24.

-Vijyat

[Aussiebear]

Hmmmm... Then try the following concept;

For Total Count

=Sumproduct(--(B3:I24="F"),--(B3:I24="V"),--(B3:I24="S"),--(B3:I24="Apples"))

For individual Sections Count

=Sumproduct(--(B3:I24="F"),--(B3:I24="Apples"))
=Sumproduct(--(B3:I24="V"),--(B3:I24="Apples"))
=Sumproduct(--(B3:I24="S"),--(B3:I24="Apples"))

[vijyat]

Hi Aussiebear.
K, so I tried that and it's giving me a value of 0. Visually if I try to find out the unique values of Fruits then the answer for Total Fruits should be 7: apples, guava, banana, passion fruit, orange, watermelon & peach; from the total of 12 Fruits ( where 5 of them are duplicated in the entire range.)
Similarly the visual count for unique values of vegetables should be 4 out of a total of 5 fruits since carrot is duplicated and so on it should follow the same suite for Spices.

Sorry if I wasn't clear in my last reply, i took "apple" as an example to specify that it should be counted as 1 out of the 4 total "apples" count.