Problem with Summing Index Function

[bombergrindman]

Hi all,
I'm having trouble summing an index function. I have two worksheets, WS 1 is a summary worksheet, worksheet 2 is a database
WS1
A........ ...........B ..................C
1 Client .........Product .........Sum
2 Bob ............Shoes ................?
3 Bob............. T Shirts ............?
4 Bob ............Jeans ..............?
5 Joe ............Shoes
6 Joe ............T Shirts
7 Ken .............Jeans



WS2


A B C D
Transaction .........Client .......Product ........Amount
1 521................. Bob .........T Shirt ............10
2 522 ................Bob .........T Shirt .............12
3 523................. Ken......... Jeans............. 75
4 524............... Bob ............Shoes ...........50
5 525 ...............Bob............. Shoes........... 62
6 526 ...............Joe ..............T Shirts ......15
7 527 ..............Joe ...........T Shirt................. 20
8 528 ..............Bob .............Jeans ..............100


I need to be able to total the transactions on WS1 sorted by client and product.


So if I wanted to find all the transactions in WS2 that were Bob and Shoes, and my formula is in C1 I have unsuccesfully so far

 {=sum(index(WS2!D1:D8,match(WS1!A1&B1,WS2!B1:B8&WS2!C1:C8,0),0))}

and

=sumif(WS2!D1:D8,WS1!A1=WS2!B1:B8,WS1!B1=WS2!C1:C8,)

Any help is greatly appreciated, I've been working on this for 6 hours now and it's incredibly frustrating.

 

[Trevor G]

Welcome to the Forum,
Look at this solution I am using SUMPRODUCT

Sheet4

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 78px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Transaction</TD><TD>Client</TD><TD>Product</TD><TD>Amount</TD><TD> </TD><TD> </TD><TD>Client</TD><TD>Product</TD><TD>Totals</TD></TR><TR style="HEIGHT: 23px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">521</TD><TD>Bob</TD><TD>T Shirt</TD><TD style="TEXT-ALIGN: right">10</TD><TD> </TD><TD> </TD><TD>Bob</TD><TD>T Shirt</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">22</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">522</TD><TD>Bob</TD><TD>T Shirt</TD><TD style="TEXT-ALIGN: right">12</TD><TD> </TD><TD> </TD><TD>Bob</TD><TD>Shoes</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">112</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">523</TD><TD>Ken</TD><TD>Jeans</TD><TD style="TEXT-ALIGN: right">75</TD><TD> </TD><TD> </TD><TD>Bob</TD><TD>Jeans</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">100</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">524</TD><TD>Bob</TD><TD>Shoes</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD><TD> </TD><TD>Ken</TD><TD>Jeans</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">75</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">525</TD><TD>Bob</TD><TD>Shoes</TD><TD style="TEXT-ALIGN: right">62</TD><TD> </TD><TD> </TD><TD>Joe</TD><TD>T Shirts</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">35</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">526</TD><TD>Joe</TD><TD>T Shirts</TD><TD style="TEXT-ALIGN: right">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">527</TD><TD>Joe</TD><TD>T Shirts</TD><TD style="TEXT-ALIGN: right">20</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">528</TD><TD>Bob</TD><TD>Jeans</TD><TD style="TEXT-ALIGN: right">100</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>I2</TD><TD>=SUMPRODUCT(($B$2:$B$9=G2)*($C$2:$C$9=H2)*($D$2:$D$9))</TD></TR><TR><TD>A3</TD><TD>=A2+1</TD></TR><TR><TD>I3</TD><TD>=SUMPRODUCT(($B$2:$B$9=G3)*($C$2:$C$9=H3)*($D$2:$D$9))</TD></TR><TR><TD>A4</TD><TD>=A3+1</TD></TR><TR><TD>I4</TD><TD>=SUMPRODUCT(($B$2:$B$9=G4)*($C$2:$C$9=H4)*($D$2:$D$9))</TD></TR><TR><TD>A5</TD><TD>=A4+1</TD></TR><TR><TD>I5</TD><TD>=SUMPRODUCT(($B$2:$B$9=G5)*($C$2:$C$9=H5)*($D$2:$D$9))</TD></TR><TR><TD>A6</TD><TD>=A5+1</TD></TR><TR><TD>I6</TD><TD>=SUMPRODUCT(($B$2:$B$9=G6)*($C$2:$C$9=H6)*($D$2:$D$9))</TD></TR><TR><TD>A7</TD><TD>=A6+1</TD></TR><TR><TD>A8</TD><TD>=A7+1</TD></TR><TR><TD>A9</TD><TD>=A8+1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

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

 

[Yahya]

if you are using Excel 2007 or Excel 2010, Try this

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Transaction	Client	Product	Amount			Client	Product	Totals
2	521	Bob	T Shirt	10			Bob	T Shirt	22
3	522	Bob	T Shirt	12			Bob	Shoes	112
4	523	Ken	Jeans	75			Bob	Jeans	100
5	524	Bob	Shoes	50			Ken	Jeans	75
6	525	Bob	Shoes	62			Joe	T Shirts	35
7	526	Joe	T Shirts	15
8	527	Joe	T Shirts	20
9	528	Bob	Jeans	100
Sheet6

Excel 2007

Cell Formulas
Range	Formula
I2	=SUMIFS($D$2:$D$9,$B$2:$B$9,G2,$C$2:$C$9,H2)

 

[Peter_SSs]

Welcome to the MrExcel board!

Have you considered using Excel's built-in Pivoit Table feature? It is designed for just this sort of job.

I've done one in columns F:G and another one in columns I:N to demonstrate a couple of different layouts.

Although I have done this on the same sheet as the original data, you can send the results to a different sheet if you want.

The layout will look better than this if you do it yourself. Excel jeanie does not show Pivot Tables all that well.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Transaction	Client	Product	Amount		Row Labels	Sum of Amount		Sum of Amount	Column Labels
2	521	Bob	T Shirt	10		Bob	234		Row Labels	Jeans	Shoes	T Shirt	T Shirts	Grand Total
3	522	Bob	T Shirt	12		Jeans	100		Bob	100	112	22		234
4	523	Ken	Jeans	75		Shoes	112		Joe				35	35
5	524	Bob	Shoes	50		T Shirt	22		Ken	75				75
6	525	Bob	Shoes	62		Joe	35		Grand Total	175	112	22	35	344
7	526	Joe	T Shirts	15		T Shirts	35
8	527	Joe	T Shirts	20		Ken	75
9	528	Bob	Jeans	100		Jeans	75
10						Grand Total	344
11
Pivot Table