A Sum Index (Match maybe?) Formula

[NyxieMoon]

Hello!

I am looking for a formula to assist me with this:

I have a file with three sheets; let's say Sheet1, Sheet2, and Sheet3. I would like Sheet1 as my main page and where I would like the formula to be set up. This will include the customer number and a place for the formula to return.

Sheet2 has the customer number with the dollar amount spent.

Sheet3 has the customer number with the dollar amount currently in transaction.

I need Sheet1 to have a formula to Index/Match both Sheet2 and Sheet3 values to the customer number and add the totals together. A tricky thing about this is that there are multiple entries on both Sheet2 and Sheet3 with the customer number and I need all the totals added together.

I tried =sum(index(Sheet1!$C$4:$C$15,match(A1,Sheet1!$B$4:$B$15,0))) to see if it would total just one page of entries and it did not; but I need two pages of entries!

Any help or assistance would be great!

Thank you!

 

[Scott T]

You can use sumif

Excel 2010
	A	B
1	cust #
2	123	23
Sheet1

Cell Formulas
Range	Formula
B2	=SUMIF(Sheet2!A2:A6,A2,Sheet2!B2:B6)+SUMIF(Sheet3!A2:A6,A2,Sheet3!B2:B6)

Excel 2010
	A	B
1	cust #	amt
2	123	7
3	654	3
4	321	11
5	1946	66
6	123	10
Sheet2

Excel 2010
	A	B
1	cust #	cur amt
2	123	5
3	654	5
4	321	6
5	1946	1
6	123	1
Sheet3

 

[oldbrewer]

[TABLE="width: 1186"]
<colgroup><col><col span="14"></colgroup><tbody>[TR]
[TD="colspan: 4"]Sheet2 has the customer number with the dollar amount spent.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Sheet3 has the customer number with the dollar amount currently in transaction.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I need Sheet1 to have a formula to Index/Match both Sheet2 and Sheet3 values to the customer number and add the totals together. A tricky thing about this is that there are multiple entries on both Sheet2 and Sheet3 with the customer number and I need all the totals added together.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]col C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col H[/TD]
[TD]col I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col M[/TD]
[TD]col N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sheet3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]number[/TD]
[TD]totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]number[/TD]
[TD]spent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]number[/TD]
[TD]trans[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]cust1[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD]row 12[/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]250[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]cust2[/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]1680[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]105[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]275[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]110[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]325[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]375[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]130[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]135[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]425[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]140[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]450[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]145[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]475[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]150[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 23[/TD]
[TD][/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]155[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]525[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]formula giving 1500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 10"]=SUMPRODUCT(($H$12:$H$23=C12)*($I$12:$I$23))+SUMPRODUCT(($M$12:$M$23=C12)*($N$12:$N$23))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this what you want[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]if so just add sheet references to the formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[NyxieMoon]

Yes! Thank you! This is what I need!