Formula Question -Dynamic Table Transaction Total column

[CJAM]

I am struggling with a formula in table.

Currently, I have transactional data where I select/type one (1) possible value out of five (5) in column A.

Value two (2) and value five (5) need to have column B and C multiplied for a total value in column D. (number of pieces * the price per piece = total)

Value one (1), three (3), and four (4) need to transfer the value in C into column D. This value is a total price regardless of the number of pieces.

I've included a picture to visually described what I would like to happen, which is one (1) formula in column D that figures out which formula to use based on the value (Ex1-Ex5) in column A.

I've done two (2) sumif functions to get the columns on the right: (The only way I could figure out how to do this, so far)
=sumif($A2,"Ex2",$B2)*$C2
=sumif($A2,"Ex1",$B2)*1

[TABLE="width: 523"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD]Possible Value
[/TD]
[TD]# of pieces[/TD]
[TD]price[/TD]
[TD]total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ex1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]$100.00[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$100.00[/TD]
[/TR]
[TR]
[TD]Ex2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$0.50[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]Ex5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]$0.25[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]Ex1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]$20.00[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$20.00[/TD]
[/TR]
[TR]
[TD]Ex3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]$0.10[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]Ex1[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]$40.00[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$40.00[/TD]
[/TR]
[TR]
[TD]Ex4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]$0.75[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD]Ex5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]$0.01[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance.

 

[AhoyNC]

Like this?

Excel Workbook
	A	B	C	D
1	Possible Value	# of pieces	price	total
2	Ex1	50	$100.00	$100.00
3	Ex2	2	$0.50	$1.00
4	Ex5	5	$0.25	$1.25
5	Ex1	25	$20.00	$20.00
6	Ex3	10	$0.10	$0.10
7	Ex1	35	$40.00	$40.00
8	Ex4	3	$0.75	$0.75
9	Ex5	25	$0.01	$0.25
Sheet

 

[asking]

Hi CJAM,
If I understand you correctly this should work.

In cell D2 type =Total
Keep that cell selected and go to formulas menu, click name manager, click new, in the name box enter Total
In the refers to box type this formula ~
=IF(OR($A2="EX2",$A2="EX5"),$B2*$C2,$C2)
Now click OK and it will change the formula to show sheet name.

Now just fill down cell D2 for as many cells as required.

Cheers.