Formula Needed
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
Best Answer

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.
 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.
 Your first column will be a SingleSelect Dropdown for the state.
 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.  Your third and fourth columns are the Start and End Dates.
 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.
 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.  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, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast
Answers

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.
 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.
 Your first column will be a SingleSelect Dropdown for the state.
 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.  Your third and fourth columns are the Start and End Dates.
 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.
 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.  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, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

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.

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.
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

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

@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.
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

Unfortunately I just can't get it done.

Amber, This is what comes up when I try and set the formula in the column.
=IF(State@row = "CA", 60, 50)

And this comes up with the other formula.

Got it working!!! Thank you so much.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!