Sumproduct? multiple columns with a criteria

[gobblechops]

Hi all,

I sure this will be an easy task for an expert....

C3:C30 contains prices
D3:D30 contains a name
F3:AH30 contain quantities

For example, row 3 has a name "Ken" in column C, a cost of £2.00 in column D and a varying number of quantities in columns F:AH.

I would like to multiply the cost by the total number of quantity on that row but also sum the total of all the lines with "Ken" using the same criteria as above. Different rows have different costs in column D and different total quantities in the rows.

Thank you in advance!

 

[Eric W]

Something like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Name		Ken	2		5	3	1		10		7	10
4	Ken		Al	1		1	7	3	3	5	9	1	8
5			Mary	2		8	1	2	6	1	8	7	2
6	Total		Ken	3		7	3	5	6	6	7	9	4
7	328.5		Jane	2		1	3	5	5	1	8	6	8
8			Elayne	1		3	3	9	4	2	7	2	2
9			Ken	2.75		4	2	4	10	7	3	2	10
10			Jack	4		7	8	2	8	1	10	1	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula A7 =SUMPRODUCT((D3:D10)*(C3:C10=A4)*F3:M10) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[gobblechops]

Hi Eric,

Thank you for your quick reply. I am glad to say, about 5 minutes after posting, I managed to get it and we had the same formula (although mine had more brackets?).

 

[Eric W]

Glad you got it figured out. Sometimes you need parentheses/brackets, sometimes Excel ignores the ones you don't. In looking at my formula again, I realize that I didn't need the ones around D3:D10.