Excel Formula CountIf

[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

 

[Frank Kabel]

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)))

 

[Aladin Akyurek]

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"}

 

[macropod]

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