How can I total actual hours complete by who it is assigned to?

I currently am able to calculate the total hours assigned to each person, but I would like to show how many hours are complete and that still need to be completed for each individual. This is the formula I am using to calculate total hours assigned and this is working. =SUMIF({Assigned To 3}, Primary24, {Estimated Hours 3}) + SUMIF({Assigned To 4}, Primary24, {Estimated Hours 4}) + SUMIF({Assigned To 5}, Primary24, {Estimated Hours 5}) + SUMIF({Assigned To}, Primary24, {Estimated Hours}). Now I want to add the aspect of if the status is complete. Please let me know if you have any ideas!

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

Hi @EJ End

I would change each group of SUMIF to SUMIFS, adding status condition and moving the {Estimated Hours #} range in front.

For example;

SUMIFS({Estimated Hours 3}, {Assigned To 3}, Primary24, {Status},"complete")

https://help.smartsheet.com/function/sumifs

Or I would use COLLECT to create a range with the status condition to be used in the SUM function.

For example;

SUM(COLLECT{Estimated Hours 3}, {Assigned To 3}, Primary24, {Status},"complete"))

https://help.smartsheet.com/function/collect

All comments

che.rabajante

Hello @EJ End

Use SUMIFS function to sum values based on multiple criteria.

Hope this helps!🙂

che

jmyzk_cloudsmart_jp

Hi @EJ End

I would change each group of SUMIF to SUMIFS, adding status condition and moving the {Estimated Hours #} range in front.

For example;

SUMIFS({Estimated Hours 3}, {Assigned To 3}, Primary24, {Status},"complete")

https://help.smartsheet.com/function/sumifs

Or I would use COLLECT to create a range with the status condition to be used in the SUM function.

For example;

SUM(COLLECT{Estimated Hours 3}, {Assigned To 3}, Primary24, {Status},"complete"))

https://help.smartsheet.com/function/collect

EJ End

That worked!

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.