Count field size name .

[Sunline]

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
	S	U
1	Fsz	Race Name
2	4	FARMING SHOW-12-1 weekdays on Radio Sport 2170
3	4	FARMING SHOW-12-1 weekdays on Radio Sport 2170
4	4	FARMING SHOW-12-1 weekdays on Radio Sport 2170
5	4	FARMING SHOW-12-1 weekdays on Radio Sport 2170
6	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
7	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
8	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
9	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
10	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
11	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
12	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
13	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
14	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
15	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
16	11	TAURANGA CHRISTMAS RACES-BOOK NOW 1400
17	9	THEWAYYOUARE@WINDSOR PARK 1400
18	9	THEWAYYOUARE@WINDSOR PARK 1400
19	9	THEWAYYOUARE@WINDSOR PARK 1400
20	9	THEWAYYOUARE@WINDSOR PARK 1400
21	9	THEWAYYOUARE@WINDSOR PARK 1400
22	9	THEWAYYOUARE@WINDSOR PARK 1400
23	9	THEWAYYOUARE@WINDSOR PARK 1400
24	9	THEWAYYOUARE@WINDSOR PARK 1400
25	9	THEWAYYOUARE@WINDSOR PARK 1400
26	7	YARDMASTER PUMPS BY REID & HARRISON 1200
27	7	YARDMASTER PUMPS BY REID & HARRISON 1200
28	7	YARDMASTER PUMPS BY REID & HARRISON 1200
29	7	YARDMASTER PUMPS BY REID & HARRISON 1200
30	7	YARDMASTER PUMPS BY REID & HARRISON 1200
31	7	YARDMASTER PUMPS BY REID & HARRISON 1200
32	7	YARDMASTER PUMPS BY REID & HARRISON 1200
33	6	AZTECH SIGN-FAREWELL NOONS 1400
34	6	AZTECH SIGN-FAREWELL NOONS 1400
35	6	AZTECH SIGN-FAREWELL NOONS 1400
36	6	AZTECH SIGN-FAREWELL NOONS 1400
37	6	AZTECH SIGN-FAREWELL NOONS 1400
38	12	LENNY GOORD FAREWELL
39	12	LENNY GOORD FAREWELL
40	12	LENNY GOORD FAREWELL
41	12	LENNY GOORD FAREWELL
42	12	LENNY GOORD FAREWELL
43	12	LENNY GOORD FAREWELL
44	12	LENNY GOORD FAREWELL
45	12	LENNY GOORD FAREWELL
46	12	LENNY GOORD FAREWELL
47	12	LENNY GOORD FAREWELL
48	12	LENNY GOORD FAREWELL
49	12	LENNY GOORD FAREWELL
50	3	CHRISTMAS PARTIES @ ELLERSLIE 1200
51	3	CHRISTMAS PARTIES @ ELLERSLIE 1200
52	3	CHRISTMAS PARTIES @ ELLERSLIE 1200
53	8	COCA-COLA 1400
54	8	COCA-COLA 1400
55	8	COCA-COLA 1400
56	8	COCA-COLA 1400
57	8	COCA-COLA 1400
58	8	COCA-COLA 1400
59	8	COCA-COLA 1400
60	8	COCA-COLA 1400
61	13	SCHWEPPES GREAT NORTHERN HURDLE
62	13	SCHWEPPES GREAT NORTHERN HURDLE
63	13	SCHWEPPES GREAT NORTHERN HURDLE
64	13	SCHWEPPES GREAT NORTHERN HURDLE
65	13	SCHWEPPES GREAT NORTHERN HURDLE
66	13	SCHWEPPES GREAT NORTHERN HURDLE
67	13	SCHWEPPES GREAT NORTHERN HURDLE
68	13	SCHWEPPES GREAT NORTHERN HURDLE
69	13	SCHWEPPES GREAT NORTHERN HURDLE
70	13	SCHWEPPES GREAT NORTHERN HURDLE
71	13	SCHWEPPES GREAT NORTHERN HURDLE
72	13	SCHWEPPES GREAT NORTHERN HURDLE
73	13	SCHWEPPES GREAT NORTHERN HURDLE
74	4	LINDAUER 2100
75	4	LINDAUER 2100
76	4	LINDAUER 2100
77	4	LINDAUER 2100
78	8	MEADOW FRESH GREAT NORTHERN STEEPLECHASE
79	8	MEADOW FRESH GREAT NORTHERN STEEPLECHASE
80	8	MEADOW FRESH GREAT NORTHERN STEEPLECHASE
81	8	MEADOW FRESH GREAT NORTHERN STEEPLECHASE
82	8	MEADOW FRESH GREAT NORTHERN STEEPLECHASE
83	8	MEADOW FRESH GREAT NORTHERN STEEPLECHASE
84	8	MEADOW FRESH GREAT NORTHERN STEEPLECHASE
85	8	MEADOW FRESH GREAT NORTHERN STEEPLECHASE
Sheet1

 

[Steph77]

=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

 

[Rick Rothstein]

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.

 

[vds1]

In S2

=COUNTIF(U:U,U2)

drag down

 

[Sunline]

ADVERTISEMENT

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 .

 

[Steph77]

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?

 

[Sunline]

ADVERTISEMENT

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 .

 

[Steph77]

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

 

[Sunline]

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
	S	T	HR
1	Fsz	Race Name	Before
2	16	TRAC SPORTS BAR MATAMATA 2000	30
3	16	TRAC SPORTS BAR MATAMATA 2000	30
4	16	TRAC SPORTS BAR MATAMATA 2000	30
5	16	TRAC SPORTS BAR MATAMATA 2000	30
6	16	TRAC SPORTS BAR MATAMATA 2000	30
7	16	TRAC SPORTS BAR MATAMATA 2000	30
8	16	TRAC SPORTS BAR MATAMATA 2000	30
9	16	TRAC SPORTS BAR MATAMATA 2000	30
10	16	TRAC SPORTS BAR MATAMATA 2000	30
11	16	TRAC SPORTS BAR MATAMATA 2000	30
12	16	TRAC SPORTS BAR MATAMATA 2000	30
13	16	TRAC SPORTS BAR MATAMATA 2000	30
14	16	TRAC SPORTS BAR MATAMATA 2000	30
15	16	TRAC SPORTS BAR MATAMATA 2000	30
16	16	TRAC SPORTS BAR MATAMATA 2000	30
17	16	TRAC SPORTS BAR MATAMATA 2000	30
18	15	RACING TAURANGA THIS SATURDAY 1200	15
19	15	RACING TAURANGA THIS SATURDAY 1200	15
20	15	RACING TAURANGA THIS SATURDAY 1200	15
21	15	RACING TAURANGA THIS SATURDAY 1200	15
22	15	RACING TAURANGA THIS SATURDAY 1200	15
23	15	RACING TAURANGA THIS SATURDAY 1200	15
24	15	RACING TAURANGA THIS SATURDAY 1200	15
25	15	RACING TAURANGA THIS SATURDAY 1200	15
26	15	RACING TAURANGA THIS SATURDAY 1200	15
27	15	RACING TAURANGA THIS SATURDAY 1200	15
28	15	RACING TAURANGA THIS SATURDAY 1200	15
29	15	RACING TAURANGA THIS SATURDAY 1200	15
30	15	RACING TAURANGA THIS SATURDAY 1200	15
31	15	RACING TAURANGA THIS SATURDAY 1200	15
32	15	RACING TAURANGA THIS SATURDAY 1200	15
33	11	TRAC SPORTS BAR ROTORUA 1400	11
34	11	TRAC SPORTS BAR ROTORUA 1400	11
35	11	TRAC SPORTS BAR ROTORUA 1400	11
36	11	TRAC SPORTS BAR ROTORUA 1400	11
37	11	TRAC SPORTS BAR ROTORUA 1400	11
38	11	TRAC SPORTS BAR ROTORUA 1400	11
39	11	TRAC SPORTS BAR ROTORUA 1400	11
40	11	TRAC SPORTS BAR ROTORUA 1400	11
41	11	TRAC SPORTS BAR ROTORUA 1400	11
42	11	TRAC SPORTS BAR ROTORUA 1400	11
43	11	TRAC SPORTS BAR ROTORUA 1400	11
44	9	RACING MATAMATA 10 DECEMBER 1600	9
45	9	RACING MATAMATA 10 DECEMBER 1600	9
46	9	RACING MATAMATA 10 DECEMBER 1600	9
47	9	RACING MATAMATA 10 DECEMBER 1600	9
48	9	RACING MATAMATA 10 DECEMBER 1600	9
49	9	RACING MATAMATA 10 DECEMBER 1600	9
50	9	RACING MATAMATA 10 DECEMBER 1600	9
51	9	RACING MATAMATA 10 DECEMBER 1600	9
52	9	RACING MATAMATA 10 DECEMBER 1600	9
53	10	MATAMATA CHRONICLE 1200	22
54	10	MATAMATA CHRONICLE 1200	22
55	10	MATAMATA CHRONICLE 1200	22
56	10	MATAMATA CHRONICLE 1200	22
57	10	MATAMATA CHRONICLE 1200	22
58	10	MATAMATA CHRONICLE 1200	22
59	10	MATAMATA CHRONICLE 1200	22
60	10	MATAMATA CHRONICLE 1200	22
61	10	MATAMATA CHRONICLE 1200	22
62	10	MATAMATA CHRONICLE 1200	22
63	13	COLCHESTER ENGINEERING 1200	55
64	13	COLCHESTER ENGINEERING 1200	55
65	13	COLCHESTER ENGINEERING 1200	55
66	13	COLCHESTER ENGINEERING 1200	55
67	13	COLCHESTER ENGINEERING 1200	55
68	13	COLCHESTER ENGINEERING 1200	55
69	13	COLCHESTER ENGINEERING 1200	55
70	13	COLCHESTER ENGINEERING 1200	55
71	13	COLCHESTER ENGINEERING 1200	55
72	13	COLCHESTER ENGINEERING 1200	55
73	13	COLCHESTER ENGINEERING 1200	55
74	13	COLCHESTER ENGINEERING 1200	55
75	13	COLCHESTER ENGINEERING 1200	55
76	12	TAINUI PRESS DESIGN & PRINT 1400	27
77	12	TAINUI PRESS DESIGN & PRINT 1400	27
78	12	TAINUI PRESS DESIGN & PRINT 1400	27
79	12	TAINUI PRESS DESIGN & PRINT 1400	27
80	12	TAINUI PRESS DESIGN & PRINT 1400	27
81	12	TAINUI PRESS DESIGN & PRINT 1400	27
82	12	TAINUI PRESS DESIGN & PRINT 1400	27
83	12	TAINUI PRESS DESIGN & PRINT 1400	27
84	12	TAINUI PRESS DESIGN & PRINT 1400	27
85	12	TAINUI PRESS DESIGN & PRINT 1400	27
86	12	TAINUI PRESS DESIGN & PRINT 1400	27
87	12	TAINUI PRESS DESIGN & PRINT 1400	27
88	9	BELL & GRAHAM 1400	9
89	9	BELL & GRAHAM 1400	9
90	9	BELL & GRAHAM 1400	9
91	9	BELL & GRAHAM 1400	9
92	9	BELL & GRAHAM 1400	9
93	9	BELL & GRAHAM 1400	9
94	9	BELL & GRAHAM 1400	9
95	9	BELL & GRAHAM 1400	9
96	9	BELL & GRAHAM 1400	9
97	8	ELECTRICO 2000	8
98	8	ELECTRICO 2000	8
99	8	ELECTRICO 2000	8
100	8	ELECTRICO 2000	8
101	8	ELECTRICO 2000	8
102	8	ELECTRICO 2000	8
103	8	ELECTRICO 2000	8
104	8	ELECTRICO 2000	8
Sheet1

 

[Steph77]

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)