Maximum Concurrent Tasks

The COUNTIF formula will count all tasks where:

Their Start Date is before or on the End Date of the current task.
AND
Their End Date is after or on the Start Date of the current task.

=COUNTIFS([Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row) - 1

Since the formula also counts the current row, it subtracts 1 to get concurrent tasks other than itself.

https://app.smartsheet.com/b/publish?EQBCT=7b10ede926104f94a4835a20496df75b (Link to the published demo sheet. You can change the Select row to see how the formula works.)

How to use this sheet

Look for the task with many concurrent tasks, such as the task in the second row. (There are three concurrent tasks.)
Check the Select column.
Overlapped tasks will be shown with conditional formatting.
Reconsider and update the assignments or schedule to lessen the overlap.
Create reports filtered by [Assigned To] value for your subcontractors or share the sheet filters (*).

Note: Overlap formula

=IFERROR(AND([Start Date]@row <= [Selected End Date]#, [End Date]@row >= [Selected Start Date]#, [Assigned To]@row = [Selected Assinged To]#), "")

Note: Summary Field formulas

[Selected Start Date]#=IFERROR(INDEX(COLLECT([Start Date]:[Start Date], Select:Select, true), 1), "")
[Selected End Date]#=IFERROR(INDEX(COLLECT([End Date]:[End Date], Select:Select, true), 1), "")
[Selected Assigned To]#=IFERROR(INDEX(COLLECT([Assigned To]:[Assigned To], Select:Select, true), 1), "")

(*) Sheet filtered by Framer 2.

Most of this looks great and the quick response is awesome!

Here's what I'm still missing:

The first couple pictures in my example the framer is starting wall framing on one building, that will take him 7 days. But the other concurrent tasks during that time are shorter durations completed by separate crews. So I can realistically run that 7 days with only 3 crews. That's where I need some version of MAX in the formula that would return "3" into that task. Since at most during this timeframe 7/14-7/22 I would need is 3 crews on site on that Wednesday and Thursday.

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

It is pretty interesting to learn about real-world needs!

Below is the updated demo solution that takes into your requirements.

As you can see, the selected task has three overlaps, but the maximum of two overlaps will happen on August 19, 2025.

I used this approach to count the maximum number.

Get each date of the task, starting from the Start Date and ending at the End Date.
For each date, check for overlaps with other tasks that contain the date in their task range.
1. For example, first check if the Start Date, 08/12/25, has any overlaps.
2. Then, the next day, 08/13/25, and so on, until the End Date, 08/19/25.
Then, count the number of such tasks.
Finally, get the maximum number of the counts.

So, I first added 20 helper columns, [D1] to [D20], assuming 20 dates would be enough for most of the task.

As you can see, the hepper columns populate dates within the [Start Date] and [End Date] ranges.

I used the following pattern of the formula;

[D1] =[Start Date]@row
[D2] =IF([D1]@row + 1 <= [End Date]@row, [D1]@row + 1)
[D3] =IF([D2]@row + 1 <= [End Date]@row, [D2]@row + 1)
…….
[D20] =IF([D19]@row + 1 <= [End Date]@row, [D19]@row + 1)

Second, I added 20 helper columns, [C1] to [C20], to populate the number of overlap tasks, including itself, on the corresponding date. For example, [C1] will populate the number of overlaps on [D1].

[C1] =COUNTIFS([Start Date]:[Start Date], <=[D1]@row, [End Date]:[End Date], >=[D1]@row, [Assigned To]:[Assigned To], [Assigned To]@row)

[C2] =COUNTIFS([Start Date]:[Start Date], <=[D2]@row, [End Date]:[End Date], >=[D2]@row, [Assigned To]:[Assigned To], [Assigned To]@row)
…….

[C20] =COUNTIFS([Start Date]:[Start Date], <=[D20]@row, [End Date]:[End Date], >=[D20]@row, [Assigned To]:[Assigned To], [Assigned To]@row)

Finally, I used the following to calculate the maximum number.

[Max Concurrent] =MAX([C1]@row:[C20]@row) - 1

As a bonus attempt, I created those formulas to show the start and end dates of the maximum overlap dates. For example, the maximum overlap for the Roof Trusees & Decking tasks starts on August 2 and ends on August 5.

[Max Start] =INDEX([D1]@row:[D20]@row, 1, MATCH([Max Concurrent]@row + 1, [C1]@row:[C20]@row, 0))
[Max End] =INDEX([D1]@row:[D20]@row, 1, MATCH([Max Concurrent]@row + 1, [C1]@row:[C20]@row, 0) + COUNTIF([C1]@row:[C20]@row, [Max Concurrent]@row + 1) - 1)

I created those helper columns using the proprietary API tool, as adding these many columns and formulas is time-consuming.
If you find replicating those helper columns and formulas tricky, please get in touch with me for the sheet share or any other help. (My email address is on the profile page.)

It's been a minute since I've been able to circle back to this, and it was working great. However, now I'm getting the cell references over 25M error. I can't say I understand what I've read regarding this error, but it seems like my row count of ~1400 tasks is multiplying the C1-D20 columns exponentially and putting me over this limit. Any suggestions, or should I just chalk this up as not possible?

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

It looks like the "Too Many Cells Referenced" error you’re experiencing is indeed due to the exponential growth of cell references, especially from the [C1] to [C20] columns that use COUNTIFS with column-wide references (column:column). When your sheet has ~1400 tasks, the cumulative cell references can easily exceed the 25M limit.

I have updated the formulas to reduce column references significantly while achieving the same outcome to address this. Here's an explanation of the improved approach:

Formulas

[Max Concurrent] =MAX([C1]@row:[C20]@row)
[R1]to[R4] gets the row number of tasks which overlaps for a Assigned To
[R1] =IFERROR(INDEX(COLLECT(Row:Row, Row:Row, <>Row@row, [Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, <=End@row, End:End, >=Start@row), 1), "") ….
[R4] =IFERROR(INDEX(COLLECT(Row:Row, Row:Row, <>Row@row, [Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, <=End@row, End:End, >=Start@row), 4), "")
[S1]to[S4] and [E1]to[E4] gets the start date and end dates of those overlapping tasks.
[S1] =IF(ISNUMBER([R1]@row), INDEX(Start:Start, [R1]@row)) ….
[S4] =IF(ISNUMBER([R4]@row), INDEX(Start:Start, [R4]@row))
[E1] =IF(ISNUMBER([R1]@row), INDEX(End:End, [R1]@row))
….
[E4] =IF(ISNUMBER([R4]@row), INDEX(End:End, [R4]@row))
[C1] to [C20] counts the overlapping task for [D1] to [D2], using the above [S1]to[S4]and[E1]to[E4]
[C1] =IF(ISDATE([D1]@row), IF(AND([S1]@row <= [D1]@row, [E1]@row >= [D1]@row), 1, 0) + IF(AND([S2]@row <= [D1]@row, [E2]@row >= [D1]@row), 1, 0) + IF(AND([S3]@row <= [D1]@row, [E3]@row >= [D1]@row), 1, 0) + IF(AND([S4]@row <= [D1]@row, [E4]@row >= [D1]@row), 1, 0)) ….
(readable version)
[C20] =IF(ISDATE([D20]@row),
IF(AND([S1]@row <= [D20]@row, [E1]@row >= [D20]@row), 1, 0) +
IF(AND([S2]@row <= [D20]@row, [E2]@row >= [D20]@row), 1, 0) +
IF(AND([S3]@row <= [D20]@row, [E3]@row >= [D20]@row), 1, 0) +
IF(AND([S4]@row <= [D20]@row, [E4]@row >= [D20]@row), 1, 0))
[D1]to[D20] populates dates witin a task's duration
[D1] =Start@row …

[D20] =IF([D19]@row + 1 <= End@row, [D19]@row + 1)

Key Improvements in the Updated Formulas

Significant Reduction in Cell References
- Previous Method:
  [C1] to [C20] used COUNTIFS with [Start Date]:[Start Date], [End Date]:[End Date], and [Assigned To]:[Assigned To], causing exponential growth in references. For a sheet with 1400 rows, this approach generated approximately 117,600,000 references in total.
- Updated Method:
  Instead of referencing entire columns, the new formulas use COLLECT and INDEX to extract up to 4 overlapping tasks based on the same [Assigned To] value. By referencing only [S1] to [S4] and [E1] to [E4] for checking overlaps, the total cell references are reduced to about 224,000 references.
- Total Reduction: This change reduces cell references by about 117,376,000 (≈ 99.8% reduction), preventing the 25M error and improving performance significantly.
Extracting Up to 4 Overlapping Tasks Efficiently
- New Formula Approach: We now use [R1] to [R4] to store row numbers of up to 4 overlapping tasks using:
  [R1] =IFERROR(INDEX(COLLECT(Row:Row, Row:Row, <>Row@row, [Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, <=End@row, End:End, >=Start@row), 1), "")
  - This formula finds the first overlapping task and stores its row number in [R1].
  - Similarly, [R2], [R3], and [R4] find and store the next three overlapping tasks.
Using Specific Date References Instead of Column References
- Previous Method: [C1] to [C20] used COUNTIFS with column references like [Start Date]:[Start Date] to check overlaps.
- Updated Method: We now store the start and end dates of the overlapping tasks directly in [S1] to [S4] (Start Dates) and [E1] to [E4] (End Dates) using:
  [S1] =IF(ISNUMBER([R1]@row), INDEX(Start:Start, [R1]@row))
  [E1] =IF(ISNUMBER([R1]@row), INDEX(End:End, [R1]@row))
  - This eliminates the need to reference entire date columns repeatedly.
  - Only the dates of the overlapping tasks are referenced, reducing the total cell reference count dramatically.
Optimized Overlap Counting with Limited References
- Previous Approach:
  [C1] =COUNTIFS([Start Date]:[Start Date], <=[D1]@row, [End Date]:[End Date], >=[D1]@row, [Assigned To]:[Assigned To], [Assigned To]@row)
  - This formula caused a massive increase in cell references due to full-column referencing combined with multiple conditions.
- New Approach:
  [C1] =IF(ISDATE([D1]@row), IF(AND([S1]@row <= [D1]@row, [E1]@row >= [D1]@row), 1, 0) + IF(AND([S2]@row <= [D1]@row, [E2]@row >= [D1]@row), 1, 0) + IF(AND([S3]@row <= [D1]@row, [E3]@row >= [D1]@row), 1, 0) + IF(AND([S4]@row <= [D1]@row, [E4]@row >= [D1]@row), 1, 0))
  - This formula checks overlaps only against the specific four tasks instead of the entire column.
  - By using [S1] to [S4] and [E1] to [E4], we limit the references to a maximum of 4 tasks per row.
Efficient Date Management
- The [D1] to [D20] columns that generate date sequences remain the same, but benefit from the reduced overhead caused by optimized [C1] to [C20] formulas.
- Since each date now checks overlap against a maximum of 4 specific tasks instead of entire columns, this part of the formula becomes significantly lighter.

Please let me know if you need further assistance implementing these changes or have more questions!

Working through updating it now. What is the difference between the Row and Row ID columns? I didn't see either of them listed in the formulas, but could pull the "Row" column formula from the sample sheet you sent.

After adding a "Row ID" column and just autonumbering I placed the formula in that you had for "Row", knowing it's pretty much 2 redundant columns. Once I updated the rest of the formulas and it looks like I'm still over the capability of Smartsheet: