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

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!


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!