SUMIF(S), AND, IF, Checkbox formula functions

Natalie_CCC

Hi brainstrust,

I am trying to build a resource conflict formula(s) that deliver the end results of:

If a project row has overlapping dates AND has the same resource type(s) selected (different columns with check boxes) as the date-conflict project rows, return a "conflict" result.
AND If a project has a resource type(s) selected, sum the [resource allocation] rows that also have that resource type selected. And if the total of that sum highlight those rows as a conflict.

For column set up - Image 1

Some formulas I have played with:

(Found through Smartsheet Community and adapted)

=IF((ISBLANK([Project Manager.]@row)), 0, IF(COUNTIFS([Project Manager.]:[Project Manager.], @cell = [Project Manager.]@row, [Estimated Start Date]:[Estimated Start Date], @cell <= [Estimated Completion Date]@row, [Estimated Completion Date]:[Estimated Completion Date], @cell >= [Estimated Start Date]@row) > 1, JOIN([Project Manager.]@row)))

this formula works by itself.

=IF([Project Manager.]@row = 1, SUMIF([Project Manager.]@row = 1, [resource allocation]:[resource allocation]), "No Conflict")

Image 2, getting the result of "0", when it should be returning "1.6"

Any suggestions would be appreciated!

Find more posts tagged with

Formulas

Community Platform

Resource Management

Portfolio Management

Accepted answers

All comments

Natalie_CCC

Image 2

KPH

This part of your formula is the problem.

=SUMIF([Project Manager.]@row = 1, [resource allocation]:[resource allocation])

SUMIF needs a range, followed by a comma, then a criteria, then an optional range to count (with a preceding comma).

Instead of the =1 you need a comma to separate the range and the criteria.

Instead of [Project Manager.]@row you need to reference the entire column in your range.

Try this:

=IF([Project Manager.]@row = 1, SUMIF([Project Manager.]:[Project Manager.], 1, [resource allocation]:[resource allocation]), "No Conflict")

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.