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
Creative	Advanced	James Brown	CA|James Brown	#REF!
Creative	Advanced	BB King	CA|BB King	*
Creative	Basic	Jerry Seinfeld	CB|Jerry Seinfeld	*
*	*	*	*	*
Monochrome	Master	Tiberias Kirk	MM|Tiberias Kirk	*
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>
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="bgcolor: #FF99CC"]Category[/TD]
[TD="bgcolor: #FF99CC, align: center"]Level[/TD]
[TD="bgcolor: #FF99CC"]Name[/TD]
[TD="bgcolor: #FF99CC"]Helper Column[/TD]
[TD="bgcolor: #FF99CC"]YTD Points[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

</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
Cat	CB	Creative Projected Basic	Best in Show	*	*
1st Place	*	*	CB|Jerry Seinfeld
*	Creative Projected Advanced	*	*	*	*	|CA
1st Place	*	*	CA|James Brown
2nd Place	*	*	CA|BB King
*	Monochrome Projected Masters	*	*	*	*	|MM
1st Place	*	*	MM|Tiberias Kirk
2nd Place	*	*	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>
[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: center"]Entry Points[/TD]
[TD="align: center"]Place Points[/TD]

[TD="align: center"]Total[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: left"]CB[/TD]
[TD="align: left"]Jerry Seinfeld[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: left"]CA[/TD]

[TD="align: center"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: left"]CA[/TD]
[TD="align: left"]James Brown[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: left"]CA[/TD]
[TD="align: left"]BB King[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: left"]MM[/TD]

[TD="align: center"]*[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: left"]MM[/TD]
[TD="align: left"]Tiberias Kirk[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: left"]MM[/TD]
[TD="align: left"]Leonard McCoy[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</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")))