Count field size name .

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
701
Office Version
  1. 2016
Platform
  1. Windows
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 .
Excel Workbook
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
Sheet1
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
=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 :)
 
Upvote 0
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.
 
Upvote 0
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 .
 
Upvote 0
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?
 
Upvote 0
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 .
 
Upvote 0
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
 
Upvote 0
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 .
Excel Workbook
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
Sheet1
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top