Need help with this SUMIF formula that checks whole columns

Hi,

=SUMIFS([Target End Date]:[Target End Date], [Target Start Date]:[Target Start Date], "<=" & [Target End Date]@row, [Target End Date]:[Target End Date], ">=" & [Target Start Date]@row) - [Target Start Date]@row + 1

I am trying to use this formula to summarise the number of days in which other rows overlap with a specific row in terms of their timeframes

However, when I try to set this as a column formula I get error messages, need help

Find more posts tagged with

Accepted answers

All comments

Justin326326

Update:

=IF(AND([Target Start]@row <= [Target End]144, [Target End]@row >= [Target Start]144), NETWORKDAYS(MAX([Target Start]144, [Target Start]@row), MIN([Target End]144, [Target End]@row)), 0)

This formula works in finding the number of days two tasks overlap, however again the issue I am facing is I want this to be compared column wide, where the total value is summed up

prime_nathaniel

Justin326326 is your issue you just can't get it to convert? You can move stuff to summary fields or use helper columns where the value is always the same as work arounds.

Justin326326

https://community.smartsheet.com/discussion/comment/480183#Comment_480183

@prime_nathaniel

Hi,

My issue is with an example such as the code above I do not know a method to have it where this column formula would check @row (each row the formula is applied to in the column) against every other row in Target Start and Target End, in a manner where I can find the total days that other rows overlap timeline wise with @row

So if painting the house was from 01-01-25 to 23-01-25

If Putting the roof up was from 16-01-25 to 02-02-25

And moving the furniture was from 05-01-25 to 12-01-25

Lets say the @row in this case was "Painting the house" and I wanted to find how many days within its duration other tasks overlapped with it

In this case Putting the roof up would overlap for 8 days, Moving the furniture would also clash for 8 days

The output should then be 16 days.

As each task is given 6 hours per day from 01-23 for @row that would be 23 days which would mean 138 hours

As each working day is 8 hours the total hours available in the timeframe would be 184

If I considered the other tasks in the timeframe that would be 104 additional hours

That would mean total workload would actually be 242 hours meaning @row cannot be completed in the workload and I would obviously use conditional formatting to indicate that then I would reallocate tasks accordingly

Ignore the testing column, but I have something working for a similar purpose for Hours within next week, month and the orange one is where you can enter in a custom timeframe to see if you are overallocated there

jmyzk_cloudsmart_jp

Hi @Justin326326

You're on the right track! Let me first quickly summarize your situation:

Understanding Your Goal

You are trying to calculate the number of working days that other tasks overlap with a specific task's timeline.

Initially, you tried using a SUMIFS formula based on date conditions, but that approach could not properly calculate day-by-day overlaps or be set as a column formula.

Later, you switched to using:

=IF(AND([Target Start]@row <= [Target End]144, [Target End]@row >= [Target Start]144), NETWORKDAYS(MAX([Target Start]144, [Target Start]@row), MIN([Target End]144, [Target End]@row)), 0)

This worked for comparing two specific tasks (one current row vs one specific row like Row 144) to calculate overlapping working days.
However, the challenge was that you needed it to compare the current task against ALL other tasks dynamically, not just one static row.

That's where a different structure is needed — using helper columns and dynamic overlap matching.

https://app.smartsheet.com/b/publish?EQBCT=c94146e8ade2410f852b99c0e0014547

Overview of How I Built It

Because Smartsheet formulas cannot dynamically loop through every row inside one cell, I added helper columns labeled 1, 2, 3, and 4.

Each helper column calculates the number of overlapping working days between:

The current task (the row where the formula is written), and
The first, second, third, or fourth overlapping task, respectively.

For example:

Helper 1: working days of overlap with the first overlapping task,
Helper 2: working days of overlap with the second overlapping task,
Helper 3: working days of overlap with the third overlapping task,
Helper 4: working days of overlap with the fourth overlapping task.

Below is an example formula used for calculating the overlap days with the third overlapping task.

=IF([is Parent]@row, "", IFERROR(NETWORKDAYS( MAX(Start@row, INDEX(Start:Start, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 3))), MIN(End@row, INDEX(End:End, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 3)))), 0))

=IF(

    [is Parent]@row, 

    "", 

    IFERROR(

        NETWORKDAYS(

            MAX(

                Start@row, 

                INDEX(

                    Start:Start, 

                    INDEX(

                        COLLECT(

                            [Row ID]:[Row ID], 

                            [is Parent]:[is Parent], false, 

                            [Row ID]:[Row ID], <>[Row ID]@row, 

                            Start:Start, <=End@row, 

                            End:End, >=Start@row

                        ), 

                        3

                    )

                )

            ),

            MIN(

                End@row, 

                INDEX(

                    End:End, 

                    INDEX(

                        COLLECT(

                            [Row ID]:[Row ID], 

                            [is Parent]:[is Parent], false, 

                            [Row ID]:[Row ID], <>[Row ID]@row, 

                            Start:Start, <=End@row, 

                            End:End, >=Start@row

                        ), 

                        3

                    )

                )

            )

        ),

        0

    )

)

Detailed Step-by-Step Formula Breakdown

Step 1: IF([is Parent]@row, "", ...)
If the row is a parent row, return blank. Parent tasks usually aggregate their children, not individual dates.

Step 2: COLLECT(...)
Gather all Row IDs that meet:

[is Parent] = false (only child tasks),
Not the current row itself,
Date ranges actually overlap (Start and End comparison).

Step 3: INDEX(COLLECT(...), 3)
Pick the third overlapping task from the collected list.

Step 4: INDEX(Start:Start, INDEX(...)) and INDEX(End:End, INDEX(...))
Find the Start and End dates of the third overlapping task.

Step 5: MAX(Start dates) and MIN(End dates)
Calculate the overlapping period:

Start at the later of the two Start Dates,
End at the earlier of the two End Dates.

Step 6: NETWORKDAYS(...)
Count the number of working days (Monday to Friday) between those two dates.

Step 7: IFERROR(..., 0)
If there’s no third overlap found (or any error occurs), return 0 cleanly.

The key point of this solution is the COLLECT formula that gets all the Row IDs that overlap with the current row.

COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row)

With this collection, we can dynamically get the Start and End date of overlapping rows using the INDEX function.

Note: If you move the rows, use the MATCH function to get the Row number instead of [Row ID].

Fomalas of the demo sheet

[is Parent] =COUNT(CHILDREN()) > 0

[Overlap Rows] =IF([is Parent]@row, "", JOIN(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), ", "))

[Overlap Rows Count] =IF([is Parent]@row, "", COUNT(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row)))

[Total Overlap Days] =IF([is Parent]@row, "", SUM([1]@row:[4]@row))

[1] =IF([is Parent]@row, "", IFERROR(NETWORKDAYS(MAX(Start@row, INDEX(Start:Start, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 1))), MIN(End@row, INDEX(End:End, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 1)))), 0))
[2] =IF([is Parent]@row, "", IFERROR(NETWORKDAYS(MAX(Start@row, INDEX(Start:Start, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 2))), MIN(End@row, INDEX(End:End, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 2)))), 0))
[3] =IF([is Parent]@row, "", IFERROR(NETWORKDAYS(MAX(Start@row, INDEX(Start:Start, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 3))), MIN(End@row, INDEX(End:End, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 3)))), 0))
[4] =IF([is Parent]@row, "", IFERROR(NETWORKDAYS(MAX(Start@row, INDEX(Start:Start, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 4))), MIN(End@row, INDEX(End:End, INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], false, [Row ID]:[Row ID], <>[Row ID]@row, Start:Start, <=End@row, End:End, >=Start@row), 4)))), 0))

Justin326326

@jmyzk_cloudsmart_jp

Hi, thanks so much for that, I will test that out, but that would apear to be what I would need.

Could I ask with the number of columns I have for "overlapping" such as 1, 2, 3, 4 - should I not try to make as many of those columns as possible considering the possibility of more than 4 tasks that overlap?

Justin326326

@jmyzk_cloudsmart_jp

Hi thanks so much, I have implemented the code

One thing I did not specify due to not wanting to use names was that these tasks are assigned to individuals and I obviously need to check them against their own tasks

So, for example jack's tasks that overlap with jack's tasks and the same for jill

Justin326326

@ jmyzk_cloudsmart_jp

Hi, so another update.

I think there is an issue with your formula in the helper columns where these are just comparing the Target Start and End values within the row instead of the other rows, which is producing incorrect output values

=IF([is Parent]@row, "", IFERROR(NETWORKDAYS(MAX([Target Start]@row, INDEX([Target Start]:[Target Start], INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), 1))), MIN([Target End]@row, INDEX([Target End]:[Target End], INDEX(COLLECT([Row ID]:[Row ID], [is Parent]:[is Parent], 0, [Assigned To Helper]:[Assigned To Helper], =[Assigned To Helper]@row, [Row ID]:[Row ID], <>[Row ID]@row, [Target Start]:[Target Start], <=[Target End]@row, [Target End]:[Target End], >=[Target Start]@row), 1)))), 0))

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.