Totalling by Names

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194
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
BCDEFGHIJKLMNOPQRST
1LUREHURDLESCOURSING
2EntryPointsEntryPointsEntryPointsEntryAttendanceStartsTotalracepoints
3Twister6Twister6Smithy6Twister12
4Ajay5Ajay5Alex5Ajay10
5Cartman5Cartman4Benson5Cartman11
6Max4April1Doc4O'Toole13
7O'Toole4Cheese4O'Toole4Ace6
8Rose4Mouse4Rose4etcetc
9Chase3Ace3Ace3
10Molly3O'Toole5Mouse3
11Mouse3Rusty3Tyson3
12Tyson3Timu3Vanilla3
13Buddy2Chase3Benson2
14Entry2Harry2Cartman2
Point Score
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

TradBum

New Member
Joined
Mar 4, 2004
Messages
15
Heya Ted,

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

Smitty (PennySaver)
 

tedc

Board Regular
Joined
Oct 31, 2003
Messages
194
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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
ABCDEFGH
1EntryPointsRaceRace(All)
2Twister6LURE
3Ajay5LURESumofPoints
4Cartman5LUREEntryTotal
5Max4LUREO'Toole13
6O'Toole4LURETwister12
7Rose4LURECartman11
8Chase3LUREAjay10
9Molly3LUREMouse10
10Mouse3LURERose8
11Tyson3LUREBenson7
12Buddy2LURETyson6
13Entry2LUREAce6
14Twister6HURDLESChase6
15Ajay5HURDLESSmithy6
16Cartman4HURDLESAlex5
17April1HURDLESGrandTotal100
18Cheese4HURDLES
19Mouse4HURDLESTop12onlyselected
20Ace3HURDLES
21O'Toole5HURDLES
22Rusty3HURDLES
23Timu3HURDLES
24Chase3HURDLES
25Harry2HURDLES
26Smithy6COURSING
27Alex5COURSING
28Benson5COURSING
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

Board Regular
Joined
Oct 13, 2003
Messages
144
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,692
Members
425,230
Latest member
DzOus

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
Top