Multiply Different Cells by Different Cells depending on Drop Down Selection

Jeff Pindak

Hi,

Sorry i dont know how best to phrase this so i will give an example best i can. In attached picture the Columns "Phase of Work" and " Rate" are drop downs. Ive changed the names of actual columns to be non industry specific.

Based on the selection of phase in the drop down at Column A i want it to multiply the rate times the number of units in the corresponding Column C-F and total in the "Amount Paid" Column.

So if the drop down "Phase of Work" has selected Phase 1 I need "Rate" * "Phase 1 Units". If the "Phase of Work" has selected Phase 2 i need "Rate" * "Phase 2 Units" and so forth.

See Below

Paul Newcome

Ok. Your original screenshot did post, but now that you mention where you want the formula to go it makes more sense to me. Sorry about that.

Try this...

=Rate@row * IF([Phase of Work]@row = "Phase 1", [Phase 1 Units]@row, IF([Phase of Work]@row = "Phase 2", [Phase 2 Units]@row, IF([Phase of Work]@row = "Phase 3", [Phase 3 Units]@row, IF([Phase of Work]@row = "Phase 4", [Phase 4 Units]@row))))

Jeff Pindak

I should add that due to how these items are calculated from other sheets there will be values in all columns C-F, but i need those values ignored and only the Phase for the drop down ive selected to be totaled.

New to functions/formulas so sorry im botching the explanation, but i believe that makes enough since.

I have tried to accomplish this on my own and every combination of piecing together formulas from the help just gives me back errors.

Paul Newcome

Are you able to provide a screenshot that has some manually added data that shows exactly what you are trying to accomplish?

Jeff Pindak

Thought i had a screen shot rough example in excel sorry.

Hopefully this attaches. In below example sense Phase 1 is selected, i would want

Amount paid to sum $150.

Paul Newcome

Ok. Your original screenshot did post, but now that you mention where you want the formula to go it makes more sense to me. Sorry about that.

Try this...

=Rate@row * IF([Phase of Work]@row = "Phase 1", [Phase 1 Units]@row, IF([Phase of Work]@row = "Phase 2", [Phase 2 Units]@row, IF([Phase of Work]@row = "Phase 3", [Phase 3 Units]@row, IF([Phase of Work]@row = "Phase 4", [Phase 4 Units]@row))))

Jeff Pindak

It did work, thanks!

Paul Newcome

Happy to help. 👍️