Totalling by Names

[tedc]

What code or formula is needed for the ‘Entries’ (Columns B, G, L), ‘Points’ (Columns C,H,M)
for each individual name to be totalled. The Entry’s name to appear in Column O and Total race points to appear in Column T
The lists can be quite long
Below is example only

Thanks
Ted

JR Race Daze2.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	LURE					HURDLES					COURSING
2	Entry	Points				Entry	Points				Entry	Points				Entry	Attendance	Starts	Totalracepoints
3	Twister	6				Twister	6				Smithy	6				Twister			12
4	Ajay	5				Ajay	5				Alex	5				Ajay			10
5	Cartman	5				Cartman	4				Benson	5				Cartman			11
6	Max	4				April	1				Doc	4				O'Toole			13
7	O'Toole	4				Cheese	4				O'Toole	4				Ace			6
8	Rose	4				Mouse	4				Rose	4				etc			etc
9	Chase	3				Ace	3				Ace	3
10	Molly	3				O'Toole	5				Mouse	3
11	Mouse	3				Rusty	3				Tyson	3
12	Tyson	3				Timu	3				Vanilla	3
13	Buddy	2				Chase	3				Benson	2
14	Entry	2				Harry	2				Cartman	2
Point Score

 

[TradBum]

Heya Ted,

Try SUMIF. I think that it'll fit your needs.

Smitty (PennySaver)

 

[tedc]

Hi Smitty
That would be okay 'cept there may be 70-80 names, halve of which would change on race days

Hows the house going?

Ted

 

[Ekim]

Ted,

Since all your Names and Points are populated by links to other worksheets, it may be a simple matter to create another master schedule in columnar format, and then use a Pivot Table.

Book1
	A	B	C	D	E	F	G	H
1	Entry	Points	Race			Race	(All)
2	Twister	6	LURE
3	Ajay	5	LURE			SumofPoints
4	Cartman	5	LURE			Entry	Total
5	Max	4	LURE			O'Toole	13
6	O'Toole	4	LURE			Twister	12
7	Rose	4	LURE			Cartman	11
8	Chase	3	LURE			Ajay	10
9	Molly	3	LURE			Mouse	10
10	Mouse	3	LURE			Rose	8
11	Tyson	3	LURE			Benson	7
12	Buddy	2	LURE			Tyson	6
13	Entry	2	LURE			Ace	6
14	Twister	6	HURDLES			Chase	6
15	Ajay	5	HURDLES			Smithy	6
16	Cartman	4	HURDLES			Alex	5
17	April	1	HURDLES			GrandTotal	100
18	Cheese	4	HURDLES
19	Mouse	4	HURDLES			Top12onlyselected
20	Ace	3	HURDLES
21	O'Toole	5	HURDLES
22	Rusty	3	HURDLES
23	Timu	3	HURDLES
24	Chase	3	HURDLES
25	Harry	2	HURDLES
26	Smithy	6	COURSING
27	Alex	5	COURSING
28	Benson	5	COURSING
Sheet1

Field “Race” to Page
Field “Entry” to Row
Field “Points” to Data

After getting an initial PT:
Right click a name
Select Advanced
Click the radio button for “Top 10 Autoshow”
Select how many to show in the list
Select Descending
Using Field: “Sum of Points”
OK.

You could automate all of the above with a macro.

HTH

Mike

 

[howzat]

Hi Ted,

Here's a formula approach using the recommended add-in morefunc.XLL - found at http://longre.free.fr/english/index.html using the UNIQUEVALUES()
formula:



From your example, highlight the range Q3:Q28 and array enter the following formula:

=UNIQUEVALUES(IF(ISTEXT(B3:M23),B3:M23,"")) - confirmed with Ctrl + Alt + Enter.

Enter the following formula in T3 and copy down:
=SUMIF($B$3:$L$14,Q3,$C$3:$M$14)

Hope this helps