I
instapatzer
Hello, all. I am an experienced programmer but a rookie Excel user.
I wonder if someone can help me with a simple problem?
I am the scorekeeper for my son's Little League team and I want
to keep track of the team's statistics. I have two tables per game,
showing the hitting and pitching statistics in the usual way, e.g.,
AB R H RBI
Fred 5 2 3 2
and so on. I have my spreadsheet set up with each game in
a separate worksheet.
What I want is a "season total" table that sums up the statistics
for all of the games. Since the players' names will appear in
different orders from game to game, my first shot at this was
to use a series of VLOOKUPs, e.g.,
=VLOOKUP($A1, Batting1, COLUMN(), FALSE)+VLOOKUP($A1, Batting2,
COLUMN(), FALSE)
where Batting1 and Batting2 are the tables of batting information
for games 1 and 2. Since not every player appears in every game,
this actually has to be =IF(ISERROR(VLOOKUP(...)), 0, VLOOKUP(...))
+...
in case a name is missing from one of the tables.
Copying this formula across my "season total" table gives me
the right answer, but as the number of games increases, the
formula gets increasingly long and clumsy, so I am looking for
a better way. What I want in effect is a loop:
total = 0
for each game {
if (player appears in table) {
total += statistic for this game
}
}
I could do this easily enough with a VB function, but I wanted to
avoid
that, since I am afraid that it will be a nuisance when I send the
spreadsheet
around to other people.
I thought that I could do something with an array expression, but I
couldn't
get it to work. I tried
={SUM(VLOOKUP(...,INDIRECT(List_of_tables),...))
where List_of_tables is a region containing the names of
the individual tables (Batting1, Batting2, ..), but it didn't give
me the right answer.
How would an experienced Excel user solve this problem?
I wonder if someone can help me with a simple problem?
I am the scorekeeper for my son's Little League team and I want
to keep track of the team's statistics. I have two tables per game,
showing the hitting and pitching statistics in the usual way, e.g.,
AB R H RBI
Fred 5 2 3 2
and so on. I have my spreadsheet set up with each game in
a separate worksheet.
What I want is a "season total" table that sums up the statistics
for all of the games. Since the players' names will appear in
different orders from game to game, my first shot at this was
to use a series of VLOOKUPs, e.g.,
=VLOOKUP($A1, Batting1, COLUMN(), FALSE)+VLOOKUP($A1, Batting2,
COLUMN(), FALSE)
where Batting1 and Batting2 are the tables of batting information
for games 1 and 2. Since not every player appears in every game,
this actually has to be =IF(ISERROR(VLOOKUP(...)), 0, VLOOKUP(...))
+...
in case a name is missing from one of the tables.
Copying this formula across my "season total" table gives me
the right answer, but as the number of games increases, the
formula gets increasingly long and clumsy, so I am looking for
a better way. What I want in effect is a loop:
total = 0
for each game {
if (player appears in table) {
total += statistic for this game
}
}
I could do this easily enough with a VB function, but I wanted to
avoid
that, since I am afraid that it will be a nuisance when I send the
spreadsheet
around to other people.
I thought that I could do something with an array expression, but I
couldn't
get it to work. I tried
={SUM(VLOOKUP(...,INDIRECT(List_of_tables),...))
where List_of_tables is a region containing the names of
the individual tables (Batting1, Batting2, ..), but it didn't give
me the right answer.
How would an experienced Excel user solve this problem?