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!

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)