Best Way to Sum Values in Variable Positions Across Multiple Sheets?

Basically, I'm trying to tabulate stats that would be manually entered on a grid, where the values would be in a different column for each sheet. Think of it like a box score in baseball, where the lineup is different each game. I want the main team page to automatically calculate a player's total hits, but I can't just do a 3D sum to the same cell on each page, because the individual player won't always be on the same cell (but the row will always be constant). On a discussion I found about a similar problem, one suggestion was to do SUMIF on each sheet, with each player's total for that sheet being in a fixed position, so then you can just do a SUM for that cell across every sheet. It seems like that will work, but I'm just not sure if it's the most expeditious way (also, I'm slightly OCD about having the sheets as "clean" as possible, even with cells that aren't immediately visible). Another suggestion said something about =SUMPRODUCT(SUMIF(INDIRECT... It seemed like that would be a lot more straightforward, but it's a bit too far above my current understanding to figure out exactly how that string of nested formulae works.

sum_variable_sample.xls

So, in the attached example, each page after the first has a record of "stuff" and "things" for Bob, John, Steve and Ted, and I'm looking for the best way to have the first page automatically tabulate each person's total of Stuff and Things, even though their placement changes on each page.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

We would need to know the real sheet names in your real file. I suspect the real sheet names are not Sheet2, Sheet3 and Sheet4.

There are a few ways to handle the sheet names.

If the sheet names follow a sequential pattern like the default sheet names the we can "build" the sheet names directly into the formula.

If the sheet names are random then you either have to hardcode them into the formula or list the sheet names in a range of cells.

Here's an example of listing the sheet names in a range of cells:

Data Range

	A	B	C	D
1
2	Sheet Names		Stuff	Things
3	Sheet2	Bob	3	5
4	Sheet3	Steve	4	4
5	Sheet4	John	5	8
6		Ted	2	3
7

This formula entered in C3:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A$3:A$5&"'!C2:F2"),B3,INDIRECT("'"&A$3:A$5&"'!C3:F3")))

This formula entered in D3:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A$3:A$5&"'!C2:F2"),B3,INDIRECT("'"&A$3:A$5&"'!C4:F4")))

Select C3:D3 and copy down as needed.

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.