Sum Across Worksheets

[dwgnome]

Following up on a similar post regarding summing scores across multiple sheets, I need to modify the formula that Aladin was so kind to provide to fit a different summary sheet. This time I need to split out the different skill levels within a category. Aladin used a way to concatenate the category and name to come up with a condition for SUMPRODUCT(SUMIF construct. I tried to make it match to the new format but I keep getting #REF!


See the small example below.

Total YTD2

*	A	B	C	D	E
7	Category	Level	Name	Helper Column	YTD Points
8	Creative	Advanced	James Brown	CA|James Brown	#REF!
9	Creative	Advanced	BB King	CA|BB King	*
10	Creative	Basic	Jerry Seinfeld	CB|Jerry Seinfeld	*
11	*	*	*	*	*
12	Monochrome	Master	Tiberias Kirk	MM|Tiberias Kirk	*
13	Monochrome	Master	Leonard McCoy	MM|Leonard McCoy	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 96px;"><col style="width: 109px;"><col style="width: 113px;"><col style="width: 143px;"><col style="width: 75px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D8 =LEFT(A8,1)&LEFT(B8,1)&"|"&C8 E8 =SUMPRODUCT(SUMIF(INDIRECT("'"&ShtList&"'!J9:J120"),D$8,INDIRECT("'"&ShtList&"'!H9:H120"))) D9 =LEFT(A9,1)&LEFT(B9,1)&"|"&C9 D10 =LEFT(A10,1)&LEFT(B10,1)&"|"&C10 D12 =LEFT(A12,1)&LEFT(B12,1)&"|"&C12 D13 =LEFT(A13,1)&LEFT(B13,1)&"|"&C13 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Feb 2014

*	B	C	D	E	F	G	H	I	J
8	Cat	CB	Creative Projected Basic	Entry Points	Place Points	Best in Show	Total	*	*
9	CB	Jerry Seinfeld	1st Place	1	4	*	5	*	CB|Jerry Seinfeld
10	*	CA	Creative Projected Advanced	*	*	*	*	*	|CA
11	CA	James Brown	1st Place	1	4	*	5	*	CA|James Brown
12	CA	BB King	2nd Place	1	3	*	4	*	CA|BB King
13	*	MM	Monochrome Projected Masters	*	*	*	*	*	|MM
14	MM	Tiberias Kirk	1st Place	2	4	*	6	*	MM|Tiberias Kirk
15	MM	Leonard McCoy	2nd Place	1	3	*	4	*	MM|Leonard McCoy

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 32px;"><col style="width: 136px;"><col style="width: 215px;"><col style="width: 110px;"><col style="width: 110px;"><col style="width: 38px;"><col style="width: 89px;"><col style="width: 29px;"><col style="width: 170px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula J9 =B9&"|"&C9 J10 =B10&"|"&C10 J11 =B11&"|"&C11 J12 =B12&"|"&C12 J13 =B13&"|"&C13 J14 =B14&"|"&C14 J15 =B15&"|"&C15 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


ShtList refers to the Sheet names (Jan 2014, Feb 2014 etc.).

Would appreciate the help in modifying the formula. I still use Excel 2003.

 

[FDibbins]

See if you can modify this formula for what you want...
=SUMPRODUCT(SUMIF(INDIRECT("'"&BldgTabs&"'!T6:T1000"),C794,INDIRECT("'"&BldgTabs&"'!M6:M1000")))

Create a list of all your sheet names, and give that list a range name (I called it BlgTabs)

 

[dwgnome]

Thanks for your input, when I used yours and changed the named and ranges to fit my data and got the same error message. Your formula solution is essentially the same as mine, so I don't see what the difference was.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[XOR LX]

Hi.

Have you double-checked to make sure that all of the tabs listed in ShtList actually exist?

You can't define it as e.g. {"Jan 2014","Feb 2014","Mar 2014","Apr 2014"} if only say 2 or 3 of those tabs actually exist.

Regards

 

[dwgnome]

Thanks for responding. Yes all of the tabs exist. I did notice there is an unexplained difference between the concatenated fields as there seems to be an extra space after the vertical line delimiter.

 

[XOR LX]

And you've checked for unwanted extra spacing in all of your data entries?

Regards

 

[dwgnome]

Yes I checked that too, no unwanted spaces.
If you want I can post the file for access when I get home from work.
Or private message you the file for your inspection.