Formula Needed

Bernard Oakley

Want to select a state (Example: CA = $60 per day) Start date column (1/24/22), end date column (1/27/22) Sum total in next column making total $180. How can I make a formula? Hopefully I have explained it well.

3 days x $60=$180

Amber Eakin

Hello, @Bernard Oakley ! You can do this with a couple of helper columns. You'll have 7 columns before you hide the ones you don't need to see.

1. Enable Dependencies. You won't need the predecessor row, but that Duration row will give you the total number of days you'll need to calculate the Total Sum.
2. Your first column will be a Single-Select Dropdown for the state.
3. Your second column will be a helper column. I called this "State $." This is how your sheet will know a dollar amount based on state. You'll use nested IF formulas. In my example, I only used two states, so my formula is =IF(State@row = "WV", 50, IF(State@row = "CA", 60)) Set that formula to a Column Formula so that it automatically calculates. You'll hide this column once you have the Sum Total formula set up.
4. Your third and fourth columns are the Start and End Dates.
5. The fifth column is your Duration. Once you enable Dependences, Duration and Predecessor will automatically appear as columns. You can hide the Predecessor. You'll hide the Duration column once you have the Sum Total formula set up.
6. The sixth column is your Sum Total. You'll multiple the helper column "State $" by the Duration column. =[State $]@row * Duration@row Set this as a Column Formula. Change this column to Currency to show a dollar amount.
7. Hide your helper columns so that you're left only with the State, Start Date, End Date, and Sum Total columns.

Before hiding the helper columns:

After hiding the helper columns:

I hope this helps!

Amber

Amber Eakin

Hello, @Bernard Oakley ! You can do this with a couple of helper columns. You'll have 7 columns before you hide the ones you don't need to see.

1. Enable Dependencies. You won't need the predecessor row, but that Duration row will give you the total number of days you'll need to calculate the Total Sum.
2. Your first column will be a Single-Select Dropdown for the state.
3. Your second column will be a helper column. I called this "State $." This is how your sheet will know a dollar amount based on state. You'll use nested IF formulas. In my example, I only used two states, so my formula is =IF(State@row = "WV", 50, IF(State@row = "CA", 60)) Set that formula to a Column Formula so that it automatically calculates. You'll hide this column once you have the Sum Total formula set up.
4. Your third and fourth columns are the Start and End Dates.
5. The fifth column is your Duration. Once you enable Dependences, Duration and Predecessor will automatically appear as columns. You can hide the Predecessor. You'll hide the Duration column once you have the Sum Total formula set up.
6. The sixth column is your Sum Total. You'll multiple the helper column "State $" by the Duration column. =[State $]@row * Duration@row Set this as a Column Formula. Change this column to Currency to show a dollar amount.
7. Hide your helper columns so that you're left only with the State, Start Date, End Date, and Sum Total columns.

Before hiding the helper columns:

After hiding the helper columns:

I hope this helps!

Amber

Bernard Oakley

Amber,

Thank you so much. Your formula will work great because CA is $60 all other states are $50. I'm going to try and get this to work now.

Amber Eakin

Even better, @Bernard Oakley ! You can use a simple IF formula if every state but CA is 50.

=IF(State@row = "CA", 60, 50)

Here's a screenshot of this formula in action. I left the "State $" helper column unhidden for the screenshot so that you can see it works properly. You would hide this after you have everything set up.

Bernard Oakley

Amber,

How and where do I input the formula? Sorry first time doing a formula.

Amber Eakin

@Bernard Oakley No problem! You'd put it in the "State $" column instead of the one I initially created. The nested formulas are necessary if you have multiple scenarios. For instance, you'd need that if CA were $60, WV were $50, NC were $55, and so on. In this case, because only CA is an outlier, you create the new formula.

You're saying IF the State column is CA, then the State $ column should have 60. If the State column is anything other than CA, then the State $ column should have 50.

Bernard Oakley

Unfortunately I just can't get it done.

Bernard Oakley

Amber, This is what comes up when I try and set the formula in the column.

=IF(State@row = "CA", 60, 50)

Bernard Oakley

And this comes up with the other formula.

Bernard Oakley

Got it working!!! Thank you so much.