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

Customer	Record Type	Item	Qty	Price
Cust1	SI	Item1	2	1
Cust1	SC	Item1	1	1
Cust2	SI	Item2	3	2
Cust2	SI	Item1	5	1
Cust3	SI	Item2	2	2
Cust1	SI	Item2	2	2

<tbody>
</tbody>

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)