Prorate and Sum in one function
Looking for help on how to use prorate function by Month with one formula for the following data structure
Raw data sheet: One row per project:
Start date; End date; $
Metric rollup sheet
Start of Month; End of Month; Month; Year; Prorated $:
I know I can do this but not sure if I can prorate in the same formula
=SUMIFS({$}, {Start Date}, <=[End of Month]@row, {End Date}, >=[Start of Month]@row)
Answers
-
Hi @Mark S Krebs
Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
This is the structure: 2 sheets:
Raw data sheet: One row per project:
Columns:
1) Start date
2)End date
3) $
Metric rollup sheet
Columns:
1) Start of Month
2) End of Month
3) Month (calculation)
4) Year (calculation)
5) Prorated $ (calculation I want): Prorate $ if {Start Date}, <=[End of Month]@row, {End Date}, >=[Start of Month]@row
I can sum if but can't figure out how to incoporate a prorate function
-
Please share a screenshot of your sheets (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
or you can call me on my Email : Bassam.k@mobilproject.it
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Can you explain how you want to prorate this information?
Are you meaning that in the source sheet you need to first distribute the cost over the number of months the row specifies so that when you SUM the data (based on the month in your second sheet) you don't have extra costs from other months?
If so, since this is based on your source sheet data, you would need a helper column in that source sheet to first identify the cost per-day throughout the task. Then you can SUM this helper column and multiply it by the number of days for the month you're searching for.
This would be an example of the formula to identify the cost-per-day in the source sheet:
=[$ Column]@row / NETDAYS([Start Date]@row, [End Date]@row)
Does this make sense? Let me know if I've misunderstood what you're looking to do.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!