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.

 

[XOR LX]

I think it may be a case where seeing the actual workbook is necessary, yes.

Regards

 

[dwgnome]

Could you please take a look. Thank you.


https://www.dropbox.com/s/0beck7my0wd21f4/2014 Competition Point TallyV5.xls?dl=0

 

[XOR LX]

Yes I checked that too, no unwanted spaces.

I don't understand - how did you do your checks? "Linda Brown" in cell C8 of the Total YTD2 tab, for example, contains a single preceding space, as is easily verified by simply clicking in the cell.

Regards

 

[dwgnome]

Thanks for pointing out the extra spaces. That solved the problem.

 

[FDibbins]

That formula relies on a named range containing the sheet names, to work.

1. Did you create a list (somewhere) containing all your sheet names (make sure the names in the list are identical to your actual sheet names)
2. Give that list a range name (I called it BldgTabs)
3. The ranges in that formula are actually text, so you will need to adjust them by re-typing the range, rather than using the mouse
4. If you change the ranges, make sure they are all the same size (same numbwer of rows etc)

=SUMPRODUCT(SUMIF(INDIRECT("'"&BldgTabs&"'!T6:T1000"),C794,INDIRECT("'"&BldgTabs&"'!M6:M1000")))