Counting the number of occurrences

%3CLINGO-SUB%20id%3D%22lingo-sub-2175269%22%20slang%3D%22en-US%22%3ECounting%20the%20number%20of%20occurrences%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2175269%22%20slang%3D%22en-US%22%3E%3CP%3ESee%20below%20for%20examples%3A%3C%2FP%3E%3CP%3E-%20How%20would%20I%20write%20a%20formula%20to%20count%20the%20number%20of%20occurrences%20where%20the%20Group%20and%20Common%20fields%20have%20the%20same%20value%3F%3C%2FP%3E%3CP%3E-%20If%20Group%20is%20blank%2C%20output%20the%20number%20of%20occurrences%20using%20only%20using%20Common%20field%20only%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EGroup%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3ECommon%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2229px%22%3EABC%3C%2FTD%3E%3CTD%20height%3D%2229px%22%3EHello%3C%2FTD%3E%3CTD%20height%3D%2229px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABC%3C%2FTD%3E%3CTD%3EHello%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABC%3C%2FTD%3E%3CTD%3EHello%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EEFG%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EBye%20Bye%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EOla%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3ECCC%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EEEE%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2229px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDDD%3C%2FTD%3E%3CTD%3EFFF%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDDD%3C%2FTD%3E%3CTD%3EFFF%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2175269%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

See below for examples:

- How would I write a formula to count the number of occurrences where the Group and Common fields have the same value?

- If Group is blank, output the number of occurrences using only using Common field only

 

GroupCommon 
ABCHello3
ABCHello3
ABCHello3
EFGBye Bye1
 Ola1
CCCEEE1
DDDFFF2
DDDFFF2
   

 

2 Replies

@cubd8 

In C2:

=IF(A2="",COUNTIF($B$2:$B$9,B2),COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,B2))

Adjust the ranges as needed, then fill down.

@cubd8 

Not exactly what is required, just in case

image.png