Prorate and Sum in one function

Mark S Krebs

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)

Find more posts tagged with

Accepted answers

All comments

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.

Mark S Krebs

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

@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 : Bassam.k@mobilproject.it

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.