How to add another calculation to an If statement and Index/Match

Hi,

Hopefully this will be an easy one. My brain is going completely blank on this.

I have a formula that is basically calculating/selecting one of three values based on which is not empty. It is based on the user checking the "Included" box to trigger an index/match formula to show the cost (Hourly, ,Unit or Fixed), and then the user inputs the number of hours, number of units or nothing if it is a fixed cost. Then the Estimated Costs field will show which of the costs are populated.

I need to add a "% markup" field that will either do one of the following (I am not sure which one yet so I'm asking for help on both formulas)

I would like to multiply the value the user inputs in the "% Markup" field by the "Estimated Cost" and use that as the "Estimated Cost" total if there is a value in the "% Markup" field. I know I can use a helper field but I'd rather not if I don't have to. So hopefully it will work.

OR

I would like to multiply the value the user inputs in the "% Markup" field by the "Hourly Cost" or the "Unit Cost" or the "Fixed Cost" if there is a value in the "% Markup" field. I think too many "OR"s will make this way too cumbersome so if I have to add additional "% Markup" fields (one for each of the costs) I am willing to do that. But I'm not sure how to do the multiplication using the Index/Match formula.

Here is a mockup of the table so you can see what I have to start:

Included

Project ID | Project | Category

Hours

Hourly Cost

Units

Unit Cost

Fixed Cost

% Markup

Estimated Costs

x

Plans & Specifications

10

$ 150.00

$ 1,500.00

Plan Review

x

Permits

$ 2,000.00

$ 2,000.00

x

Survey

1

$ 300.00

$ 300.00

My formulas are as follows:

Hourly Cost:

=IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0)))

Unit Cost:

=IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0)))

Fixed Cost:

=IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0)))

Estimated Costs:

=IFERROR(IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)), "")

I hope this makes sense. Thank you in advance!

Lisa

Best Answer

  • AdamSYNH
    AdamSYNH ✭✭✭✭
    Answer ✓

    Hi Lisa,

    Try the below, personally I think scenario 1 below is cleaner and easier to understand for a viewer other than yourself.

    Scenario 1 - Total Estimated Costs:

    =IFERROR(IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) + IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) * [% Markup]@row, "")

    Scenario 2 - % Markup applied to each cost type:

    Hourly Cost:

    =IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

    Unit Cost:

    =IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

    Fixed Cost:

    =IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

    Many thanks,

    Adam

    Adam Collins

    Sr Clinical Development Operations Analyst

    Syneos Health

Answers

  • AdamSYNH
    AdamSYNH ✭✭✭✭
    Answer ✓

    Hi Lisa,

    Try the below, personally I think scenario 1 below is cleaner and easier to understand for a viewer other than yourself.

    Scenario 1 - Total Estimated Costs:

    =IFERROR(IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) + IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) * [% Markup]@row, "")

    Scenario 2 - % Markup applied to each cost type:

    Hourly Cost:

    =IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

    Unit Cost:

    =IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

    Fixed Cost:

    =IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

    Many thanks,

    Adam

    Adam Collins

    Sr Clinical Development Operations Analyst

    Syneos Health

  • Murz
    Murz ✭✭✭✭

    It worked! Thank you so much. Really appreciate your time and help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!