Complex Lookup and Sum

[dwgnome]

Looking to tally scores based on name and category. However the original table layout is unconventional (Excel speaking) and I cannot change the output that comes from a webpage query. I want to sum the scores across all tabs (month) first by category (ie., CB, PB, CI, CM etc) and then by name within that category as grouped and shown below. I am using Excel 2003.

2012 Total Points by Category

*	BF	BG	BH	BI	BJ
1	Sheet	*	*	Sheet	*
2	Jan 12	*	*	Feb 12	*
3	*	*	*	*	*
4	PB	Total	*	PB	Total
5	Rich Little	6	*	Mitch Dunlap	7
6	Charlie Brown	4	*	Mia Farrow	3
7	Andrew Jackson	5	*	Rich Little	2
8	*	*	*	*	*
9	CA	*	*	CA	*
10	Mia Farrow	7	*	Beth Kahn	5
11	Charlie Brown	6	*	David Spade	5
12	Charlie Brown	2	*	Wil Hurt	4
13	Beth Kahn	3	*	Beth Kahn	2
14	*	*	*	*	*
15	CM	*	*	CM	*
16	Mitch Dunlap	7	*	Tatum Oneil	9
17	Andrew Jackson	6	*	Tatum Oneil	4
18	Steve Segal	7	*	Rich Little	2

<tbody>
</tbody>

Note that the number and type of categories are different each month.
The results should look like the following table. I added the numbers for emphasis that it should be summing.

2012 Total Points by Category

*	B	D	E	F	G	H	I	J	K
26	Summary Competition (Results)	*	*	*	*	*	*	*	*
27	*	Creative	*	*	*	Pictorial	*	*	*
28	Name	CB	CI	CA	CM	PB	PI	PA	PM
29	Tatum Oneil	*	*	*	13	*	*	*	*
30	Wil Hurt	*	*	4	*	*	*	*	*
31	Charlie Brown	*	*	8	*	4	*	*	*
32	Steve Segal	*	*	*	7	*	*	*	*
33	Rich Little	*	*	*	2	8	*	*	*
34	Andrew Jackson	*	*	*	6	5	*	*	*
35	Mitch Dunlap	*	*	*	7	7	*	*	*
36	Mia Farrow	*	*	7	*	3	*	*	*
37	*	*	*	*	*	*	*	*	*
38	David Spade	*	*	5	*	*	*	*	*
39	Beth Kahn	*	*	10	*	*	*	*	*
40	*	*	*	*	*	*	*	*	*

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula G29 =9+4 F31 =6+2 H33 =6+2 F36 =7 F39 =3+5+2 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I can only seem to query by name but cannot get around how to add referencing to the category.

=SUMPRODUCT(($BI$4:$BI$18=B$4)*1,$BJ$4:$BJ$18 )

or

=SUMIF(BI4:BI18,"Tatum Oneil",BJ4:BJ18)+SUMIF(BF4:BF18,"Tatum Oneil",BG4:BG18)

Any assistance would be appreciated.

 

[Oeldere]

I think it can be done with a pivot table.

But i can't copy the data you posted in my workbook to test it.

If you post it with excel-jeanie-html i can take a look for you.

 

[dwgnome]

Oeldere, I prefer to use formula approach instead of pivot table. The data in the OP is from Excel-jeanie and I was able to copy back into Excel. Not sure why the blanks are turned into stars and the table lines disapear, but the data is essentially representative of my example.

Any help is appreciated.

 

[dwgnome]

Bump.

 

[Robert Mika]

If you have got only 2 columns can sort your data in each tab somwhre futher to the left and the use:

look here