How to calculate hours for tasks marked 100%?

SteveRhythm

Hello,

In my Sheet I have a column for Task % Complete and another column for Total ETS Effort.

The Total ETS Effort column contains the number of hours associated with each task.

I'm trying to figure out a formula that would give me the total number of hours for each task that is 100% complete.

Any help would be greatly appreciated!

thank you!

Find more posts tagged with

Formulas

Accepted answers

All comments

Michael Culley

That would be =sumif([Task % Complete]:[Task % Complete], 1,[total ETS effort]:[total ETS effort])

SteveRhythm

@Michael Culley incredibly helpful thank you very much. If I wanted to adjust the formula so it adds 100% complete, 50% complete, and 25% complete, how would I modify it to support that?

Michael Culley

you would use .50 for 50% and .25 for 25%.

So instead of doing =sumif([Task % Complete]:[Task % Complete], 1,[total ETS effort]:[total ETS effort])

You would do =sumif([Task % Complete]:[Task % Complete], .50,[total ETS effort]:[total ETS effort])

=sumif([Task % Complete]:[Task % Complete], .25,[total ETS effort]:[total ETS effort])

SteveRhythm

@Michael Culley sorry I wasn't very clear. I was hoping to get one formula that would add the Total ETS Effort numbers based on the % complete.

Something that would calculate this

Total ETS Effort Task % Complete

10 100%

10 50%

10 25%

In the example above, the total would be 17.5 (10+5+2.5)

thank you again for helping. I'm new to this and I appreciate it

Michael Culley

Oh wow, how about something like this:

=sumif([Task % Complete]:[Task % Complete], 1,[total ETS effort]:[total ETS effort]) +(sumif([Task % Complete]:[Task % Complete], .50,[total ETS effort]:[total ETS effort])/2) + (sumif([Task % Complete]:[Task % Complete], .25,[total ETS effort]:[total ETS effort])/4)

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.