Sum the product of two cells in a table.

[MrTedius]

Hello All,

I have a table of a list of sales to customers and I am trying to create a formulae that will give me the total value of items sold to a customer.

Table
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Record Type[/TD]
[TD]Item[/TD]
[TD]Qty[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD]SI[/TD]
[TD]Item1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD]SC[/TD]
[TD]Item1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Cust2[/TD]
[TD]SI[/TD]
[TD]Item2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cust2[/TD]
[TD]SI[/TD]
[TD]Item1[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Cust3[/TD]
[TD]SI[/TD]
[TD]Item2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cust1[/TD]
[TD]SI[/TD]
[TD]Item2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

For example I want to find the total value of things sold where Customer=Cust1 and RecordType=SI.
The price is per unit so I need the product of (Qty x Price). For this example I am expecting to get the result 6 (2*1 + 2*2).

I know how to sum over one column, for example get the number of items sold to Cust1 using sumifs, but I can find a way of get summing the product of two cells.

Any help would be great.
Thanks

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	Criteria
2	Cust1	SI	6
3
4	Customer	Record Type	Item	Qty	Price
5	Cust1	SI	Item1	2	1
6	Cust1	SC	Item1	1	1
7	Cust2	SI	Item2	3	2
8	Cust2	SI	Item1	5	1
9	Cust3	SI	Item2	2	2
10	Cust1	SI	Item2	2	2
3a

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(A5:A10=A2),--(B5:B10=B2),D5:D10,E5:E10)