Excel Formula CountIf

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

Have a database of a Sales Rep and the different
territories for that month. Say North, South, East and
West. I know I can do an array of =MAX (COUNTIF
(DATA,DATA)) and in the cell it will show the number count
of times the most frequently occuring territory appears in
that range...BUT how do I get it to show the Name of the
Territory that it appearing the most....
Instead of 9 for the 9 times North appears, it will just
show North as it is the most frequently occuring
text...????
Any help would be appreciated.

Thanks
 
Hi
to get the most frequent text entry try the following array formula
(entered with CTRL+SHIFT+ENTER):
=INDEX(Data,MODE(MATCH(IF(Data<>"",Data),IF(Data0<>"",Data),0)))
 
Let A3:A15 house the following sample:

{"Territory";"North";"South";"East";"North";"East";"West";"North";"East";"Ea
st";"North";"North West";"South East"}

meaning: A3 = Territory, A4 = North, etc.

In B4 enter & copy down:

=IF(ISNUMBER(MATCH(A4,$A$3:A3,0)),"",COUNTIF($A$4:$A$15,A4))

In C4 enter & copy down:

=IF(N(B4),RANK(B4,$B$4:$B$15)+COUNTIF($B$4:B4,B4)-1,"")

In E1 enter: 1 [ which is the Top N value ]

In E2 enter:

=MAX(IF(INDEX(B4:B15,MATCH(E1,C4:C15,0))=B4:B15,C4:C15))-E1

which must be confirmed with control+shift+enter instead of just with enter.

In E4 enter & copy down:

=IF(ROW()-ROW($E$4)+1<=$E$1+$E$2,INDEX($A$4:$A$15,MATCH(ROW()-ROW($E$4)+1,$C
$4:$C$15,0)),"")

The result list is from E4 on. Given the sample, this list consists of:

{"North";"East"}
 
Hi Larry,

Something like:
=SUBSTITUTE(TRIM(IF(MAX(COUNTIF(Data,{"North","South","East","West"}))=COUNT
IF(Data,"North"),"North","
")&IF(MAX(COUNTIF(Data,{"North","South","East","West"}))=COUNTIF(Data,"South
"),"South","
")&IF(MAX(COUNTIF(Data,{"North","South","East","West"}))=COUNTIF(Data,"East"
),"East","
")&IF(MAX(COUNTIF(Data,{"North","South","East","West"}))=COUNTIF(Data,"West"
),"West"," "))," "," & ")
might do.

Note that this accomodates more than one value being the maximum.

Cheers
 
Back
Top