Summing Row-by-Row Data From One Worksheet into a Single Row in Another Worksheet

I'm working on a football statistics spreadsheet. I have multiple nested IF statements in what I'll call "WS1" for the first worksheet, which set up such that if any player's number between 1 and 99 is entered the player's name automatically appears in the column to the right of the player number (from a list in another worksheet). Let's use passing as an example. In the first quarter I could have several quarterbacks attempting passes and completing them. So, in WS1, Column A has the player number, Column B automatically enters the player's name (corresponding to the player's number), Column C has whether or not a pass was attempted - either a "1" for an attempt or it remains blank if the play was a running play (which is entered in another set of columns further to the right), and if the pass is completed a "1" is entered in Column D. Lets say that five (5) different quarterbacks attempt a pass in the first quarter and each completes at least one pass. Lets say QB A was 4 of out of 6. So, Rows 2 - 7 would have a line entry for QB A, on a play-by-play basis. For example, Row 2 would show an attempt in Col. C for QB A and it was complete, so there's a "1" in Col. D. After 6 passes, QB A gets hurt and QB B enters the game. QB B goes 3 out of 5. So, Rows 8 - 12 would be used, on a play-by-play basis during the game, to enter QB B's results. (Sorry to be so lengthy, but I had to set up the dilemma I now face).

On a separate worksheet (what I'll call "WS2") I want to show that, for the first quarter, QB A was 4 out of 6 - not on a row-by-row basis as is entered during the game in WS1, but on a single row. In other words, on WS2 I want to see the following on one row (say, Row 2) in Cols. A-D: Col. A - QB A's player number, Col. B - QB A's name, Col. C - QB A's total passes attempted, Col. D - QB A's passes completed. Similarly, on the next row down I want to show that QB B was 3 out of 5 - in the same fashion as QB A's totals on the row immediately above.

In essence, WS2 is a summary page for the row-by-row (which corresponds to play-by-play) data entered in WS1.

Keep in mind that any player numbered 1 through 99 could attempt and complete a pass (unrealistic, I know, but bear with me). Theoretically, I could have 99 players attempting and completing passes (again, unrealistic), so I need to be able to total perhaps multiple quarterbacks' results (or even a running back or a wide receiver throwing a pass).

Is there an easy way to do this? Do I need a macro (which I'm not adept at writing) or could something like VLOOKUP (with which I'm vaguely familiar) do this? (If I can figure out how to do this it will unlock doing the same sort of thing for rushing, receiving, etc. statistics)

Any help would be VERY, VERY, VERY MUCH appreciated. (Again, sorry for the message length.)

Thank you for the swift response! As requested I have attached a sample file. What I need is for what appears on the tabbed sheet "Offense Summary" in Quarter No. 1 to populate automatically from the row-by-row data on the tabbed sheet "Offense".

Unfortunately, that didn't work. If I change the number in "Offense" cell B3 to Player Number 6 (Justin Snyder), Cell B4 in "Offense Summary" goes blank, i.e., it doesn't pick up that different player's name or his attempts. That is the problem. "Offense Summary" needs to automatically populate from "Offense" no matter what player number is entered in Col. B of "Offense" during the game.

Sambo kid:

I'm sorry my original post was difficult to decipher. Attaching the file will, hopefully, make sense. I did as you suggested in Cell C3 by deleting the nested IF's with this formula: =VLOOKUP(B3,Lists!A:B,2,FALSE) and that works the same as the long IF's statement (but much shorter a formula to enter!) I can do likewise in columns K and P as you suggested.

By looking at the file are you now able to comprehend what I'm trying to accomplish by automatically the cells in "Offense Summary" from the row-by-row data in "Offense"?

The formula in cell D3 is simply to automatically the entering of a "1" in the pass attempt column (this is a spreadsheet that is being populated by a statistician during the course of a game, so the fewer the entries the better and that formula eliminates having to type a "1" in cell D3 if a player number is entered in B3).

Perhaps a different explanation would help. In the attached file, in cells B4 through D8 in "Offense Summary" I need to summarize the results from cells C3 through E24 in "Offense". If I had 5 quarterbacks attempting passes in the first quarter - meaning each one will have so many attempts and so many completions in "Offense" - I need to total those attempts and completions on a per-player basis in "Offense Summary". Is that any better of an explanation or does that just make it worse? (This is proving harder to explain than I first thought, obviously.)

Unfortunately, SUMIF won't do what I need. To test whether the SUMIF formulas worked I changed two of player 2's passes (Rows 17 and 18) to number 50 (whose player name in the Lists worksheet is 50) to see if "Offense Summary" would populate automatically by adding a fifth quarterback in the first quarter (i.e., number 50) and 50 didn't show up.

OK, thanks for the inputs. I appreciate the attempt. To answer the question, yes, I need quarterback names to populate automatically from "Offense" into "Offense Summary", too, along with the total attempts and total completions for each respective quarterback. (If you're not a football fan it is probably not as obvious what I'm trying to do; a football fan might more intuitively understand what I'm attempting to do . . . but maybe not.)

This formula should work there:

Please Login or Register  to view this content.