Home

Counting the number of rows by text value

Jenny-Amari
Occasional Visitor

I have a file with case numbers and case types in a given month.  I want to know which case type is the most common.  How can I get a count of the number of rows in each case type?

3 Replies
If case types are in Column C, the formula in D2 is:
=COUNTIF(C:C,C2)
Instead of C:C, you should instead use a dynamic range named CaseTypes, with this formula:
=INDEX($C$2:INDEX($C:$C,COUNTA($C:$C))
Thereafter, your formula in D2 should look like this:
=COUNTIF(CaseTypes,C2)

hello 

Shared two images hope you find the solutionsolution1.PNGSolution2.PNG@Jenny-Amari

@Jenny-Amari 

A different mindset, stemming mainly from personal prejudices! To avoid the need for helper cells, I introduced names and loaded the array formula

= COUNTIF( CaseType, CaseType )

into the 'Refers to' box for the named formula 'occurrences'.  The innovatively-named cell value 'maxOccurrences' is then given by the worksheet formula

= MAX( occurrences )

Knowing the number of occurrences I am looking for, the case type is given by

= LOOKUP( 2, 1/(occurrences=maxOccurrences), caseType )

@Gaurav_Jalan 

Thanks for the borrowed problem layout!

 

Most frequently occurring name.JPG

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies