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!

Natalie_CCC

Image 2

KPH

Hi

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")