Probably a very simple Excel question

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
With little work happening, I am devoting a ridiculous amount of attention to tracking stats on my son’s baseball team.

As such, I have a table showing the stats of each pitcher in each game listed int he top half of the workbook, broken down by game. In the bottom I want to sum up the totals across all games per player in each category. Names are in rows, stats are in columns.

So my question is what syntax do I need to use in cell range C23:K27 to have them show totals of the data in the table, aggregated appropriately? Ideally, the totals in the bottom will adjust as I add more data to the table, but I’m guessing that will be obvious.

Here’s a screen grab.
Screen Shot 2020-10-20 at 10.42.26 AM.png
 

LogansDad

Member
SoSH Member
Nov 15, 2006
18,859
Alamogordo
Try something like "=SUMIF(B4:B20,"Riley",C4:C20)

That might get you on the right track.

Edit: One other thing I might consider is putting the totaled stats to the side or on another sheet, that way you can use "B:B" instead of "B4:B20" and it will automatically sum up the stuff in that whole column as you add new players in, and you won't have to be inserting new rows at the bottom of the sheet each time you want to add an appearance.
 

Bergs

Well-Known Member
Gold Supporter
SoSH Member
Jul 22, 2005
14,373
Step 1: move your lower table to a new sheet, keeping the player names and column titles
Step 2: Formula for what is now C23, but will be moved to a new sheet

=sumif(sheet1!$B:$B,$B23,sheet1!C:C) --- Where "$B23" is actually where you moved the lower table to

That should be looking of all instances of "Riley" in Column B of sheet1, and adding up all values in column C if sheet1.

Step 3: Copy that formula and paste it in the equivilant of C23-K27 in your new table. Then do the math in columns L and M.

That will work until there have been 1,048,573 game/pitcher entries.
 
Last edited:

Humphrey

Member
SoSH Member
Aug 3, 2010
1,139
Another way is to have each worksheet represent a given ballgame. List all the players for each game whether they pitch that game or not (there will be some 0 0 0 s if you know what I mean).

So, each worksheeet has "Riley", "Neko", etc.

Then have a worksheet that totals up each of the "game" worksheets. You shouldn't have to worry about someone's name that way, as long as your roster is accurate and in the same order on each sheet....AND, if you add a player, you add the player to each sheet.
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
Try something like "=SUMIF(B4:B20,"Riley",C4:C20)

That might get you on the right track.

Edit: One other thing I might consider is putting the totaled stats to the side or on another sheet, that way you can use "B:B" instead of "B4:B20" and it will automatically sum up the stuff in that whole column as you add new players in, and you won't have to be inserting new rows at the bottom of the sheet each time you want to add an appearance.
With a little judicious use of $, I made your first version work.
 

begranter

Couldn't get into a real school
Silver Supporter
SoSH Member
Jul 9, 2007
2,145
Seems like a pivot table solution to me
This is the correct answer, especially since you have the data in tabular format already.

Either way, you can make the arrays dynamic by using the offset formula. The easiest way to do this for the pivot solution is use a named range:

1) in the "Formulas" menu, select "Name Manager", then click the "New" button"
2) In the name field, write "RawData", make sure "Scope" is Workbook.
3) The refers to field, click the up arrow to open the selection field and write the following (note using arrows to move within the text of this box will mess up the formula and I'm assuming the sheet name is Sheet1): =offset(Sheet1!$A$3,0,0,counta($A:$A)-1,counta($3:$3))
4) You can now type "RawData" into the field for the new pivot and when you hit refresh it will automatically capture any new lines that are entered, as long as they have a date in column A. (Note: the "-1" is to account for you having cell A1 populated. If you delete that, you can take out the -1. I would have suggested use column B instead, but since you have the table below, that will mess it up.

If you insist on having a summary table, as suggested above, put it on another tab. Then, you can use the offset formula in the table itself and just drag the formula across the table.
In your first cell, (in this case Riley's IP, C23) the formula would be: =SUMIF(offset($B$3,1,0,counta($B:$B)-1,1),$B23,offset(C$3,1,0,counta(C:C)-1,1)) where "$B23" is the cell where you write "Riley" in the summary table (This is all assuming the table below is removed)
 

DJnVa

Dorito Dawg
SoSH Member
Dec 16, 2010
42,358
I'd say the biggest issue isn't EXCEL it's the pitching staff.
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
I'd say the biggest issue isn't EXCEL it's the pitching staff.
I question the ER vs R column. They're kids, there have been no errors / unearned runs? Fake news.
Hah!
Our team uses an app called GameChanger which uses traditional box score layouts (likely because it is used by all ages). My son's team is in a 10 and under Cal Ripken league. The coach who inputs the game as it happens, treats all balls in play as Hits, regardless of the fielding involved. In reality, almost all the runs are unearned, but I just wanted to be able to copy data from the app to my phone without having to sift thru play by play to figure out which runs were actually earned.

Now that the Excel question has been solved -- and thank you for all the suggestions -- bonus question: Which pitcher is my son?
 

Over Guapo Grande

panty merchant
SoSH Member
Nov 29, 2005
2,017
Worcester
I'd say Neko. You always dreamed of being a knuckleballer, and wanted your kid named Neikro. Mrs. Rest would have none of that. You wanted your child to be safe, so you got rid of the IR, leaving you with Neko. #Columbo
 

BroodsSexton

Member
SoSH Member
Feb 4, 2006
9,851
guam
Please show your work.
Isn't it obvious? Appears first on the table for no good reason (not organized alphabetically, by ERA, or by IP). Plus, you wouldn't be spending so much time tracking the statistics if your son was a scrub. Gotta have the stats in front of you when you complain to the coach that your son isn't pitching enough (even though he's getting the most innings on the team) ;)
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
Isn't it obvious? Appears first on the table for no good reason (not organized alphabetically, by ERA, or by IP). Plus, you wouldn't be spending so much time tracking the statistics if your son was a scrub. Gotta have the stats in front of you when you complain to the coach that your son isn't pitching enough (even though he's getting the most innings on the team) ;)
Impressive, Mr Holmes.
 

tbrown_01923

Member
SoSH Member
Sep 29, 2006
548
I hate pivot tables. Not sure why, really.
why? they make this type of rollup easier... store the data as raw as you possibly can, set your dimensions (rows) to be any level you want to roll up to be and add the measures to the column to be yoyr aggregates (by default they ar eeither sums or counts, but you can right cliecn and change them easily).
 

BroodsSexton

Member
SoSH Member
Feb 4, 2006
9,851
guam
Impressive, Mr Holmes.
I’ve seen this episode before. My son was in 10U several years ago...Honestly I’m sort of surprised there isn’t a significantly higher number of unearned runs for Riley. I was always amazed by the guy who kept the stats for my son’s team, and literally sent long emails with advanced metrics explaining why his son should be the lead off hitter...as a 10 year old. Funny thing was, the other kids always seemed to be getting on base as a result of errors, while his son had all the hits. And his son always had the unearned runs, as a pitcher...!

But then again, maybe Riley is just DOMINATING THE LEAGUE!

Poor Neko. DFA. Maybe he can play soccer.
 

Bergs

Well-Known Member
Gold Supporter
SoSH Member
Jul 22, 2005
14,373
I hate pivot tables. Not sure why, really.
why? they make this type of rollup easier... store the data as raw as you possibly can, set your dimensions (rows) etc
Do they help with reading comprehension?

On a more serious note, I am generally an "under the hood" guy, and I like having explicit control of what I generate in xls, especially when I sprinkle in VBA, etc. Not saying pivot tables CAN'T do that, just that in my experience, you need to fight them a bit more. YMMV
 

tbrown_01923

Member
SoSH Member
Sep 29, 2006
548
On a more serious note, I am generally an "under the hood" guy, and I like having explicit control of what I generate in xls, especially when I sprinkle in VBA, etc. Not saying pivot tables CAN'T do that, just that in my experience, you need to fight them a bit more. YMMV
Ahh - when I need more horsepower than excel pivot table can provide, i jump into something like R or Python. I legitimately fear VBA for any Office based tasks (try looking at the objects behind outlook).
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
Did you blow up Ryan's arm on Day 1 and (s)he can't pitch anymore?
Updated data, with totals now accurate thanks to Bergs' help.
Screen Shot 2020-10-21 at 8.18.25 AM.png
Some background:
  • Riley is indeed my son.
  • Jack is the Head coach's son.
  • Ryan is the Asst coach's son, but he plays on an academy team up in Hartford on weekends and thus isn't available to pitch as much.
  • All the players except for OJ and Riley play on the town's travel team (well, Ryan did until he switched to the academy team). That team won the state 8U championship in 2019, finished 2nd in the New England regionals, and 6th in the nationals.
  • Jayden had been consistently the best pitcher until he threw 7 walks in 2.2 innings on Sunday in the Fall Ball semis.
 

BroodsSexton

Member
SoSH Member
Feb 4, 2006
9,851
guam
Updated data, with totals now accurate thanks to Bergs' help.
View attachment 35151
Some background:
  • Riley is indeed my son.
  • Jack is the Head coach's son.
  • Ryan is the Asst coach's son, but he plays on an academy team up in Hartford on weekends and thus isn't available to pitch as much.
  • All the players except for OJ and Riley play on the town's travel team (well, Ryan did until he switched to the academy team). That team won the state 8U championship in 2019, finished 2nd in the New England regionals, and 6th in the nationals.
  • Jayden had been consistently the best pitcher until he threw 7 walks in 2.2 innings on Sunday in the Fall Ball semis.
BTW, I didn't mean to give you shit (well, maybe a little bit!) I hope you and your son are having fun. Keeping statistics for young kids can be frustrating, because they change so much. But it can be fun, too (particularly when things are going good!)
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
BTW, I didn't mean to give you shit (well, maybe a little bit!) I hope you and your son are having fun. Keeping statistics for young kids can be frustrating, because they change so much. But it can be fun, too (particularly when things are going good!)
No offense taken! This thread, when it's not confusing me discussing the topic at hand, has been thoroughly amusing!
 

santadevil

Well-Known Member
Silver Supporter
SoSH Member
Aug 1, 2006
4,859
Saskatchestan
Updated data, with totals now accurate thanks to Bergs' help.
View attachment 35151
Some background:
  • Riley is indeed my son.
  • Jack is the Head coach's son.
  • Ryan is the Asst coach's son, but he plays on an academy team up in Hartford on weekends and thus isn't available to pitch as much.
  • All the players except for OJ and Riley play on the town's travel team (well, Ryan did until he switched to the academy team). That team won the state 8U championship in 2019, finished 2nd in the New England regionals, and 6th in the nationals.
  • Jayden had been consistently the best pitcher until he threw 7 walks in 2.2 innings on Sunday in the Fall Ball semis.
One thing I change when I do my teams stats, is the calculation of the H/9, K/9, BB/9 to the normalized inning per game they usual play
In U15 it was 7 innings, so I do those stats based on a 7 inning game, instead of 9

I also do a pitches per inning stat and a pitches per appearance stat, just to see who is being more efficient (or getting better defense behind them in some cases)
And for the innings, I set up a custom number format as #,0 0/0, which shows innings pitched as 9 1/3 or 5 2/3, which makes my brain feel better when looking at stats
 
Last edited:

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
Final calculations after they won their championship game on Sunday.

My son only went one and a third because on the second batter of the second inning, the batter hit a liner right back thru the box, catching my son right in the mouth. Caused 3 fractured teeth, multiple cuts in his mouth, and a very shook set of parents. I still have a hard time getting the image out of my head, made worse by the fact that I was filling from right behind home plate so I captured the whole thing on video.

He's doing OK now. He has a splint on his upper teeth for at least four weeks (crossing fingers that none of the teeth die). Soft food and no contact sports for 4 weeks.

Final Stats.png
 

Jim Ed Rice in HOF

Red-headed Skrub child
Gold Supporter
SoSH Member
Jul 21, 2005
4,659
Seacoast NH
If it makes you feel any better I got hit with a fly ball in 2nd grade (first day of little league tryouts in the town's first season, coaches hitting fly balls to 2nd graders, didn't end well). One of my front teeth was hanging by a root. They splinted my top teeth with some sort of mouth guard looking thing for probably that length of time. I'm now 52 and while the tooth is a shade or two different from my other teeth (not overly noticeable) it's still there. Every time the dentist's do x-rays they're kind of amazed it's still holding on.
 

santadevil

Well-Known Member
Silver Supporter
SoSH Member
Aug 1, 2006
4,859
Saskatchestan
Sorry to hear about the liner. That's always been my biggest fear when my some pitches. Back in 11U, when he was actually only 8, we were in Provincials and he took a pitch right to the face, bridge of the nose. He turned his face towards the ball, instead of away. I was the only coach qualified for the team, so I had to stay, while my wife and another parent drove him to the hospital. We were down 7 at the time and close to being eliminated, but the boys rallied back and I had my brother-in-law sending text updates every time we scored and right until the end of the game when they won

Our boy ended up being okay, didn't break his nose, but it sure was huge. He snored like a MOFO that night and his portion of the tournament was over, but he became a good cheerleader for our remaining games. Once we got home, he asked me to throw him BP, so off we went and he stood in like a champ