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

Possible Value	# of pieces	price	total
Ex1	50	$100.00			$0.00	$100.00
Ex2	2	$0.50			$1.00	$0.00
Ex5	5	$0.25			$0.00	$0.00
Ex1	25	$20.00			$0.00	$20.00
Ex3	10	$0.10			$0.00	$0.00
Ex1	35	$40.00			$0.00	$40.00
Ex4	3	$0.75			$0.00	$0.00
Ex5	25	$0.01			$0.00	$0.00

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

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.