Creating a formula to calculate estimated hours in next 14 days

Has anyone created a SUMIFS equation that could calculate the estimated hours in the next say 14 days?

Below is what I thought would work.... but it just keeps saying "0"

I want this equation to calculate the estimated hours per task/child row (HC - Project header is not checked) within the next 14 days.

=SUMIFS([Est Hrs]:[Est Hrs], [HC - Project Header]:[HC - Project Header], "0", Finish:Finish, >90

NOTE: HC - Project Header = a check box to help differentiate project header/parent information verse task/child information

Thanks for your help in advance!

Find more posts tagged with

Formulas

Accepted answers

Jenna Kiehl

Figured out an answer to my question! I included my steps and a reference image to illustrate my solution.

Steps to success:

I wanted to track these Estimated Hours Total in a new sheet to help summarize the data, so I created a new sheet that included the Date Ranges (primary column), Dates, and Est Hrs Total
I included todays date and the date ranges I was looking for
Then I added a dates column to reflect todays date, then 14, 30, 60, and 90 days out.
Then in the next column (Est Hour Total) I added the equation referencing the sheet I wanted to summarize.

Image of my real sheet:

Screen Shot 2021-03-16 at 12.26.51 PM.png

Equation (if in same sheet):

=SUMIFS([Est Hrs]:[Est Hrs], [HC - Project Header]:[HC - Project Header], "0", Finish:Finish, <Dates@row)

NOTE: The equation and reference image below is showing how the equation would work in the same sheet.

Screen Shot 2021-03-16 at 12.20.34 PM.png

All comments

Jenna Kiehl

Figured out an answer to my question! I included my steps and a reference image to illustrate my solution.

Steps to success:

I wanted to track these Estimated Hours Total in a new sheet to help summarize the data, so I created a new sheet that included the Date Ranges (primary column), Dates, and Est Hrs Total
I included todays date and the date ranges I was looking for
Then I added a dates column to reflect todays date, then 14, 30, 60, and 90 days out.
Then in the next column (Est Hour Total) I added the equation referencing the sheet I wanted to summarize.

Image of my real sheet:

Equation (if in same sheet):

=SUMIFS([Est Hrs]:[Est Hrs], [HC - Project Header]:[HC - Project Header], "0", Finish:Finish, <Dates@row)

NOTE: The equation and reference image below is showing how the equation would work in the same sheet.

Genevieve P.

Hi @Jenna Kiehl

Thank you for taking the time to post your solution and steps to success!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.