Value between dates
Hi Everyone,
New smartsheet user here, I am trying to get a total cost between dates on a running construction schedule. I have different rowcategories (Land acquisition, permitting, site work, construction, & selling) with columns showing start date, end date, and cost. All costs are positive, revenue (selling) is negative value.
I am trying to total all costs between dates by month, for example what are the running costs for the month of April. The objective is to know what our total debt is at any month.
Please see attached screenshot, I really appreciate someone's help!
Comments

Hi there,
I would create a "Helper" column that consists of a formula that assigns a month number for each row with a date. After you have that value, you can do SUMIF to calculate total dollars with a month value of 1, 2, 3 etc... (Jan, Feb, Mar) and so on. The formula options are categorized and the date functions have quite a bit to choose from. After you have your values, you can hide the helper columns to retain your original format.
CB

=sumif([Start Date]:[Start Date],Month(@cell)=4,Cost:Cost)
Without knowing what the values are hidden by the indents, and without knowing what the columns represent, that's my best guess as to what you want.

Hi CB, thanks so much for your help!
What happens if my time period lasts longer than a year, meaning I have April 2019 and April 2019 that I need to capture?
Alexandra

Thank you so much for you help, maybe the attached screenshot helps?

I have had this come up for me too, but for different reasons. I believe you could do another helper column that accounts for the year and add that to your IF formula...(I assume). There is likely an elaborate formulaic way to accomplish all of it, but I like the prefer column approach.

Since you are using a start and end date and there can be monthly and yearly overlap, you will need to breakdown how much of the project cost came in which month/year for each project. You would then need to sum those breakdowns by month/year to get your totals.

UPDATE on what I came up with based on everyone's help
1. I added a "Cost Month" column with whatever date I wanted associated with the cost for a row (in most cases the start date)
2. Under the Cash In/Out category I made a row for each month and subcategories for "Cost" and "Total Previous Month"
3. For "Cost" I manually added all the costs that fell within the "Cost Month" (Ex. =Cost1+Cost2...etc)
4. I made the row for the month have the total of the children to make it a running total
5. I used this formula to get the "Month Year" format to correspond to the "Cost Month":
=IF(MONTH([Cost Month]328) = 1, "January ", IF(MONTH([Cost Month]328) = 2, "February ", IF(MONTH([Cost Month]328) = 3, "March ", IF(MONTH([Cost Month]328) = 4, "April ", IF(MONTH([Cost Month]328) = 5, "May ", IF(MONTH([Cost Month]328) = 6, "June ", IF(MONTH([Cost Month]328) = 7, "July ", IF(MONTH([Cost Month]328) = 8, "August ", IF(MONTH([Cost Month]328) = 9, "September ", IF(MONTH([Cost Month]328) = 10, "October ", IF(MONTH([Cost Month]328) = 11, "November ", IF(MONTH([Cost Month]328) = 12, "December ")))))))))))) + YEAR([Cost Month]328)
I accomplished having the running total correspond to the month it falls in even if I change the first start date linked to all others
It's not pretty in any way shape of form but it works.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 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!