Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Count field size name .

  1. #1
    Board Regular Sunline's Avatar
    Join Date
    Oct 2007
    Location
    New Zealand
    Posts
    673
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Count field size name .

    Hello all ,

    I am needing a function that can give me the result I have in col S .

    It needs to total the count of each different name in col U .

    Thanks .
    Sheet1

     SU
    1FszRace Name
    24FARMING SHOW-12-1 weekdays on Radio Sport 2170
    34FARMING SHOW-12-1 weekdays on Radio Sport 2170
    44FARMING SHOW-12-1 weekdays on Radio Sport 2170
    54FARMING SHOW-12-1 weekdays on Radio Sport 2170
    611TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    711TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    811TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    911TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    1011TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    1111TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    1211TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    1311TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    1411TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    1511TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    1611TAURANGA CHRISTMAS RACES-BOOK NOW 1400
    179THEWAYYOUARE@WINDSOR PARK 1400
    189THEWAYYOUARE@WINDSOR PARK 1400
    199THEWAYYOUARE@WINDSOR PARK 1400
    209THEWAYYOUARE@WINDSOR PARK 1400
    219THEWAYYOUARE@WINDSOR PARK 1400
    229THEWAYYOUARE@WINDSOR PARK 1400
    239THEWAYYOUARE@WINDSOR PARK 1400
    249THEWAYYOUARE@WINDSOR PARK 1400
    259THEWAYYOUARE@WINDSOR PARK 1400
    267YARDMASTER PUMPS BY REID & HARRISON 1200
    277YARDMASTER PUMPS BY REID & HARRISON 1200
    287YARDMASTER PUMPS BY REID & HARRISON 1200
    297YARDMASTER PUMPS BY REID & HARRISON 1200
    307YARDMASTER PUMPS BY REID & HARRISON 1200
    317YARDMASTER PUMPS BY REID & HARRISON 1200
    327YARDMASTER PUMPS BY REID & HARRISON 1200
    336AZTECH SIGN-FAREWELL NOONS 1400
    346AZTECH SIGN-FAREWELL NOONS 1400
    356AZTECH SIGN-FAREWELL NOONS 1400
    366AZTECH SIGN-FAREWELL NOONS 1400
    376AZTECH SIGN-FAREWELL NOONS 1400
    3812LENNY GOORD FAREWELL
    3912LENNY GOORD FAREWELL
    4012LENNY GOORD FAREWELL
    4112LENNY GOORD FAREWELL
    4212LENNY GOORD FAREWELL
    4312LENNY GOORD FAREWELL
    4412LENNY GOORD FAREWELL
    4512LENNY GOORD FAREWELL
    4612LENNY GOORD FAREWELL
    4712LENNY GOORD FAREWELL
    4812LENNY GOORD FAREWELL
    4912LENNY GOORD FAREWELL
    503CHRISTMAS PARTIES @ ELLERSLIE 1200
    513CHRISTMAS PARTIES @ ELLERSLIE 1200
    523CHRISTMAS PARTIES @ ELLERSLIE 1200
    538COCA-COLA 1400
    548COCA-COLA 1400
    558COCA-COLA 1400
    568COCA-COLA 1400
    578COCA-COLA 1400
    588COCA-COLA 1400
    598COCA-COLA 1400
    608COCA-COLA 1400
    6113SCHWEPPES GREAT NORTHERN HURDLE
    6213SCHWEPPES GREAT NORTHERN HURDLE
    6313SCHWEPPES GREAT NORTHERN HURDLE
    6413SCHWEPPES GREAT NORTHERN HURDLE
    6513SCHWEPPES GREAT NORTHERN HURDLE
    6613SCHWEPPES GREAT NORTHERN HURDLE
    6713SCHWEPPES GREAT NORTHERN HURDLE
    6813SCHWEPPES GREAT NORTHERN HURDLE
    6913SCHWEPPES GREAT NORTHERN HURDLE
    7013SCHWEPPES GREAT NORTHERN HURDLE
    7113SCHWEPPES GREAT NORTHERN HURDLE
    7213SCHWEPPES GREAT NORTHERN HURDLE
    7313SCHWEPPES GREAT NORTHERN HURDLE
    744LINDAUER 2100
    754LINDAUER 2100
    764LINDAUER 2100
    774LINDAUER 2100
    788MEADOW FRESH GREAT NORTHERN STEEPLECHASE
    798MEADOW FRESH GREAT NORTHERN STEEPLECHASE
    808MEADOW FRESH GREAT NORTHERN STEEPLECHASE
    818MEADOW FRESH GREAT NORTHERN STEEPLECHASE
    828MEADOW FRESH GREAT NORTHERN STEEPLECHASE
    838MEADOW FRESH GREAT NORTHERN STEEPLECHASE
    848MEADOW FRESH GREAT NORTHERN STEEPLECHASE
    858MEADOW FRESH GREAT NORTHERN STEEPLECHASE


    Excel tables to the web >> Excel Jeanie HTML 4
    Using Excel version 2007

  2. #2
    Board Regular
    Join Date
    Sep 2014
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count field size name .

    =COUNTIF(U$2:U$85,U2)

    That should work, then just copy down.

    Edited as I forgot to lock the row numbers and always get range and criteria wrong way round... oops - credit to Rick Rothstein for showing the correct way round

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,562
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    25 Thread(s)

    Default Re: Count field size name .

    Put this formula in cell S2 and copy down to the bottom of your data (Row 85)...

    =COUNTIF(U$2:U$85,U2)

    Obviously, if you have more data, change the 85 to the row number containing your last data cell.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular vds1's Avatar
    Join Date
    Oct 2011
    Posts
    1,166
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count field size name .

    In S2

    =COUNTIF(U:U,U2)

    drag down
    ---------------------------------------------------------------------------
    VDS1

    Using Office 2010

    Blog :
    http://wizardvba.blogspot.com

    Currently looking for opportunities in Canada.



  5. #5
    Board Regular Sunline's Avatar
    Join Date
    Oct 2007
    Location
    New Zealand
    Posts
    673
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count field size name .

    Hi guys thanks for replies .

    I have changed the 85 to 12256 .

    When i did this only some of the results are correct . It seems to not like doing thousands of rows .

    Not sure what i may be doing wrong . Thanks .
    Using Excel version 2007

  6. #6
    Board Regular
    Join Date
    Sep 2014
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count field size name .

    Could there be extra spaces at the end of the text in column U somewhere?

    Try finding an empty column, and putting this into row 1 of it:

    =TRIM(U1)

    Copy that down, then copy and paste special (values) back into column U. See if it fixes it?

  7. #7
    Board Regular Sunline's Avatar
    Join Date
    Oct 2007
    Location
    New Zealand
    Posts
    673
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count field size name .

    Thanks Steph77 , i just tried your suggestion and pasted these back as values but still getting same results .
    Some are right but alot seem way to high . Hmmmm .
    Using Excel version 2007

  8. #8
    Board Regular
    Join Date
    Sep 2014
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count field size name .

    Hmm OK - can you attach a copy of your workbook here for download so I can have a look at it myself? (don't ask me how to attach, I'm new around here too)

    Also, list a few of the values that seem too high to you

  9. #9
    Board Regular Sunline's Avatar
    Join Date
    Oct 2007
    Location
    New Zealand
    Posts
    673
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count field size name .

    Heres a copy .
    I need to mention that my w/book was 12,256 rows and functions worked sometimes .

    Now as I am only able to post approx 100 rows as soon as I cut off the other 12,156 rows
    the function started working correctly . It changed instantly to correct answers .

    In col HR (before) these were the results before cutting the 12,256 book .

    Im totally lost with this . Grrr .
    Thanks .
    Sheet1

     STHR
    1FszRace NameBefore
    216TRAC SPORTS BAR MATAMATA 200030
    316TRAC SPORTS BAR MATAMATA 200030
    416TRAC SPORTS BAR MATAMATA 200030
    516TRAC SPORTS BAR MATAMATA 200030
    616TRAC SPORTS BAR MATAMATA 200030
    716TRAC SPORTS BAR MATAMATA 200030
    816TRAC SPORTS BAR MATAMATA 200030
    916TRAC SPORTS BAR MATAMATA 200030
    1016TRAC SPORTS BAR MATAMATA 200030
    1116TRAC SPORTS BAR MATAMATA 200030
    1216TRAC SPORTS BAR MATAMATA 200030
    1316TRAC SPORTS BAR MATAMATA 200030
    1416TRAC SPORTS BAR MATAMATA 200030
    1516TRAC SPORTS BAR MATAMATA 200030
    1616TRAC SPORTS BAR MATAMATA 200030
    1716TRAC SPORTS BAR MATAMATA 200030
    1815RACING TAURANGA THIS SATURDAY 120015
    1915RACING TAURANGA THIS SATURDAY 120015
    2015RACING TAURANGA THIS SATURDAY 120015
    2115RACING TAURANGA THIS SATURDAY 120015
    2215RACING TAURANGA THIS SATURDAY 120015
    2315RACING TAURANGA THIS SATURDAY 120015
    2415RACING TAURANGA THIS SATURDAY 120015
    2515RACING TAURANGA THIS SATURDAY 120015
    2615RACING TAURANGA THIS SATURDAY 120015
    2715RACING TAURANGA THIS SATURDAY 120015
    2815RACING TAURANGA THIS SATURDAY 120015
    2915RACING TAURANGA THIS SATURDAY 120015
    3015RACING TAURANGA THIS SATURDAY 120015
    3115RACING TAURANGA THIS SATURDAY 120015
    3215RACING TAURANGA THIS SATURDAY 120015
    3311TRAC SPORTS BAR ROTORUA 140011
    3411TRAC SPORTS BAR ROTORUA 140011
    3511TRAC SPORTS BAR ROTORUA 140011
    3611TRAC SPORTS BAR ROTORUA 140011
    3711TRAC SPORTS BAR ROTORUA 140011
    3811TRAC SPORTS BAR ROTORUA 140011
    3911TRAC SPORTS BAR ROTORUA 140011
    4011TRAC SPORTS BAR ROTORUA 140011
    4111TRAC SPORTS BAR ROTORUA 140011
    4211TRAC SPORTS BAR ROTORUA 140011
    4311TRAC SPORTS BAR ROTORUA 140011
    449RACING MATAMATA 10 DECEMBER 16009
    459RACING MATAMATA 10 DECEMBER 16009
    469RACING MATAMATA 10 DECEMBER 16009
    479RACING MATAMATA 10 DECEMBER 16009
    489RACING MATAMATA 10 DECEMBER 16009
    499RACING MATAMATA 10 DECEMBER 16009
    509RACING MATAMATA 10 DECEMBER 16009
    519RACING MATAMATA 10 DECEMBER 16009
    529RACING MATAMATA 10 DECEMBER 16009
    5310MATAMATA CHRONICLE 120022
    5410MATAMATA CHRONICLE 120022
    5510MATAMATA CHRONICLE 120022
    5610MATAMATA CHRONICLE 120022
    5710MATAMATA CHRONICLE 120022
    5810MATAMATA CHRONICLE 120022
    5910MATAMATA CHRONICLE 120022
    6010MATAMATA CHRONICLE 120022
    6110MATAMATA CHRONICLE 120022
    6210MATAMATA CHRONICLE 120022
    6313COLCHESTER ENGINEERING 120055
    6413COLCHESTER ENGINEERING 120055
    6513COLCHESTER ENGINEERING 120055
    6613COLCHESTER ENGINEERING 120055
    6713COLCHESTER ENGINEERING 120055
    6813COLCHESTER ENGINEERING 120055
    6913COLCHESTER ENGINEERING 120055
    7013COLCHESTER ENGINEERING 120055
    7113COLCHESTER ENGINEERING 120055
    7213COLCHESTER ENGINEERING 120055
    7313COLCHESTER ENGINEERING 120055
    7413COLCHESTER ENGINEERING 120055
    7513COLCHESTER ENGINEERING 120055
    7612TAINUI PRESS DESIGN & PRINT 140027
    7712TAINUI PRESS DESIGN & PRINT 140027
    7812TAINUI PRESS DESIGN & PRINT 140027
    7912TAINUI PRESS DESIGN & PRINT 140027
    8012TAINUI PRESS DESIGN & PRINT 140027
    8112TAINUI PRESS DESIGN & PRINT 140027
    8212TAINUI PRESS DESIGN & PRINT 140027
    8312TAINUI PRESS DESIGN & PRINT 140027
    8412TAINUI PRESS DESIGN & PRINT 140027
    8512TAINUI PRESS DESIGN & PRINT 140027
    8612TAINUI PRESS DESIGN & PRINT 140027
    8712TAINUI PRESS DESIGN & PRINT 140027
    889BELL & GRAHAM 14009
    899BELL & GRAHAM 14009
    909BELL & GRAHAM 14009
    919BELL & GRAHAM 14009
    929BELL & GRAHAM 14009
    939BELL & GRAHAM 14009
    949BELL & GRAHAM 14009
    959BELL & GRAHAM 14009
    969BELL & GRAHAM 14009
    978ELECTRICO 20008
    988ELECTRICO 20008
    998ELECTRICO 20008
    1008ELECTRICO 20008
    1018ELECTRICO 20008
    1028ELECTRICO 20008
    1038ELECTRICO 20008
    1048ELECTRICO 20008

    Spreadsheet Formulas
    CellFormula
    S2=COUNTIF(T$2:T$104,T2)
    S3=COUNTIF(T$2:T$104,T3)
    S4=COUNTIF(T$2:T$104,T4)
    S5=COUNTIF(T$2:T$104,T5)
    S6=COUNTIF(T$2:T$104,T6)
    S7=COUNTIF(T$2:T$104,T7)
    S8=COUNTIF(T$2:T$104,T8)
    S9=COUNTIF(T$2:T$104,T9)
    S10=COUNTIF(T$2:T$104,T10)
    S11=COUNTIF(T$2:T$104,T11)
    S12=COUNTIF(T$2:T$104,T12)
    S13=COUNTIF(T$2:T$104,T13)
    S14=COUNTIF(T$2:T$104,T14)
    S15=COUNTIF(T$2:T$104,T15)
    S16=COUNTIF(T$2:T$104,T16)
    S17=COUNTIF(T$2:T$104,T17)
    S18=COUNTIF(T$2:T$104,T18)
    S19=COUNTIF(T$2:T$104,T19)
    S20=COUNTIF(T$2:T$104,T20)
    S21=COUNTIF(T$2:T$104,T21)
    S22=COUNTIF(T$2:T$104,T22)
    S23=COUNTIF(T$2:T$104,T23)
    S24=COUNTIF(T$2:T$104,T24)
    S25=COUNTIF(T$2:T$104,T25)
    S26=COUNTIF(T$2:T$104,T26)
    S27=COUNTIF(T$2:T$104,T27)
    S28=COUNTIF(T$2:T$104,T28)
    S29=COUNTIF(T$2:T$104,T29)
    S30=COUNTIF(T$2:T$104,T30)
    S31=COUNTIF(T$2:T$104,T31)
    S32=COUNTIF(T$2:T$104,T32)
    S33=COUNTIF(T$2:T$104,T33)
    S34=COUNTIF(T$2:T$104,T34)
    S35=COUNTIF(T$2:T$104,T35)
    S36=COUNTIF(T$2:T$104,T36)
    S37=COUNTIF(T$2:T$104,T37)
    S38=COUNTIF(T$2:T$104,T38)
    S39=COUNTIF(T$2:T$104,T39)
    S40=COUNTIF(T$2:T$104,T40)
    S41=COUNTIF(T$2:T$104,T41)
    S42=COUNTIF(T$2:T$104,T42)
    S43=COUNTIF(T$2:T$104,T43)
    S44=COUNTIF(T$2:T$104,T44)
    S45=COUNTIF(T$2:T$104,T45)
    S46=COUNTIF(T$2:T$104,T46)
    S47=COUNTIF(T$2:T$104,T47)
    S48=COUNTIF(T$2:T$104,T48)
    S49=COUNTIF(T$2:T$104,T49)
    S50=COUNTIF(T$2:T$104,T50)
    S51=COUNTIF(T$2:T$104,T51)
    S52=COUNTIF(T$2:T$104,T52)
    S53=COUNTIF(T$2:T$104,T53)
    S54=COUNTIF(T$2:T$104,T54)
    S55=COUNTIF(T$2:T$104,T55)
    S56=COUNTIF(T$2:T$104,T56)
    S57=COUNTIF(T$2:T$104,T57)
    S58=COUNTIF(T$2:T$104,T58)
    S59=COUNTIF(T$2:T$104,T59)
    S60=COUNTIF(T$2:T$104,T60)
    S61=COUNTIF(T$2:T$104,T61)
    S62=COUNTIF(T$2:T$104,T62)
    S63=COUNTIF(T$2:T$104,T63)
    S64=COUNTIF(T$2:T$104,T64)
    S65=COUNTIF(T$2:T$104,T65)
    S66=COUNTIF(T$2:T$104,T66)
    S67=COUNTIF(T$2:T$104,T67)
    S68=COUNTIF(T$2:T$104,T68)
    S69=COUNTIF(T$2:T$104,T69)
    S70=COUNTIF(T$2:T$104,T70)
    S71=COUNTIF(T$2:T$104,T71)
    S72=COUNTIF(T$2:T$104,T72)
    S73=COUNTIF(T$2:T$104,T73)
    S74=COUNTIF(T$2:T$104,T74)
    S75=COUNTIF(T$2:T$104,T75)
    S76=COUNTIF(T$2:T$104,T76)
    S77=COUNTIF(T$2:T$104,T77)
    S78=COUNTIF(T$2:T$104,T78)
    S79=COUNTIF(T$2:T$104,T79)
    S80=COUNTIF(T$2:T$104,T80)
    S81=COUNTIF(T$2:T$104,T81)
    S82=COUNTIF(T$2:T$104,T82)
    S83=COUNTIF(T$2:T$104,T83)
    S84=COUNTIF(T$2:T$104,T84)
    S85=COUNTIF(T$2:T$104,T85)
    S86=COUNTIF(T$2:T$104,T86)
    S87=COUNTIF(T$2:T$104,T87)
    S88=COUNTIF(T$2:T$104,T88)
    S89=COUNTIF(T$2:T$104,T89)
    S90=COUNTIF(T$2:T$104,T90)
    S91=COUNTIF(T$2:T$104,T91)
    S92=COUNTIF(T$2:T$104,T92)
    S93=COUNTIF(T$2:T$104,T93)
    S94=COUNTIF(T$2:T$104,T94)
    S95=COUNTIF(T$2:T$104,T95)
    S96=COUNTIF(T$2:T$104,T96)
    S97=COUNTIF(T$2:T$104,T97)
    S98=COUNTIF(T$2:T$104,T98)
    S99=COUNTIF(T$2:T$104,T99)
    S100=COUNTIF(T$2:T$104,T100)
    S101=COUNTIF(T$2:T$104,T101)
    S102=COUNTIF(T$2:T$104,T102)
    S103=COUNTIF(T$2:T$104,T103)
    S104=COUNTIF(T$2:T$104,T104)


    Excel tables to the web >> Excel Jeanie HTML 4
    Using Excel version 2007

  10. #10
    Board Regular
    Join Date
    Sep 2014
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count field size name .

    Put an autofilter on the top row and check that there aren't extra copies of those names you think are showing as incorrect that you weren't aware of further down the sheet in the full size workbook.

    That or copy those 2 columns into a new workbook so you don't have to give me any private information and PM me for my email address and I'll look at it properly for you (it's hard to diagnose something like this from partial data)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •