Forum Discussion

Jenny-Amari's avatar
Jenny-Amari
Copper Contributor
Mar 17, 2019

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?

excel

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 17, 2019

    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!

     

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Mar 17, 2019
    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)

Resources