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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    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.

    PMP Certified

    [email protected]

    ☑️ 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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Mark S Krebs

    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 : [email protected]

    PMP Certified

    [email protected]

    ☑️ 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"

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Mark S Krebs

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!