vlookup, sum (2 columns ref)

January 24, 2010 at 14:29:27
Specs: Windows XP
I'm new to formulas..umm.. is the following output possible?

BASIS:
code length balance
1234 3.5 3
1234 3.7 4
1235 3.5 8
1235 3.7 2
1234 3.5 9
1234 3.7 7
1235 3.5 4
1235 3.7 5

WANTED OUTPUT: (summed balance of same code AND length)
code length balance
1234 3.5 12
1234 3.7 11
1235 3.5 12
1235 3.7 7

thanks!


See More: vlookup, sum (2 columns ref)

Report •


#1
January 24, 2010 at 17:41:05
Hi,

Assuming that your values are in columns A, B & C
starting at row 2

	A	B	C
	code	length	balance
2	1234	3.5	3
3	1234	3.7	4
4	1235	3.5	8
5	1235	3.7	2
6	1234	3.5	9
7	1234	3.7	7
8	1235	3.5	4
9	1235	3.7	5

With your 4 combinations in columns E & F
starting at row 2

	E	F
	code	length
2	1234	3.5
3	1234	3.7
4	1235	3.5
5	1235	3.7

Put this formula in cell G2:
=(SUMPRODUCT(($A$2:$A$9=G16)*($B$2:$B$9=H16)*($C$2:$C$9)))
Note the $ signs - they are important for dragging the formula:
drag the formula down to row 5

This is the result

code	length	total
1234	3.5	12
1234	3.7	11
1235	3.5	12
1235	3.7	7

Regards


Report •
Related Solutions


Ask Question