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]

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.
		col C					col H	col I				col M	col N
	sheet1						sheet2					sheet3
		number	totals				number	spent				number	trans
	cust1	456	1500		row 12		123	100				123	250
	cust2	567	1680				456	105				456	275
							234	110				234	300
							567	115				567	325
							123	120				123	350
							456	125				456	375
							234	130				234	400
							567	135				567	425
							123	140				123	450
							456	145				456	475
							234	150				234	500
					row 23		567	155				567	525
	formula giving 1500
	=SUMPRODUCT(($H$12:$H$23=C12)*($I$12:$I$23))+SUMPRODUCT(($M$12:$M$23=C12)*($N$12:$N$23))
	is this what you want
	if so just add sheet references to the formula

<colgroup><col><col span="14"></colgroup><tbody>
</tbody>

 

[NyxieMoon]

Yes! Thank you! This is what I need!