• 461K Members
• 5,858 Online
• 559K Conversations

## Counting the number of rows by text value

Occasional Visitor

# Counting the number of rows by text value

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

# Re: Counting the number of rows by text value

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)

# Re: Counting the number of rows by text value

hello

Shared two images hope you find the solution@Jenny-Amari

# Re: Counting the number of rows by text value

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 )

Thanks for the borrowed problem layout!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies