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.

 

[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.