Count Collect formula crashing large sheet - Scheduling conflict identification

Neil Egsgard

Hello,

I am running a Count(Collect) on a sheet with 8000 records. The formula calculates for a small number of records but if I make it a column formula or copy it to every cell then the sheet crashes. Does anyone know how to count thousands of rows that meet certain conditions without crashing the sheet?

Purpose of formula
To compare each record with all other records to see if there is a scheduling conflict.

Number of records
8000+
I need the analysis across all records and have reduced the record count as much as possible.

Formula crashing the sheet
=COUNT(COLLECT([Instructor date]:[Instructor date], [Instructor date]:[Instructor date], [Instructor date]@row, [End time 24h]:[End time 24h], >=[Start time 24h]@row, [Start time 24h]:[Start time 24h], <=[End time 24h]@row))

Alternative format for easier reading

=COUNT(
COLLECT(
[Instructor date]:[Instructor date],
[Instructor date]:[Instructor date], [Instructor date]@row,
[End time 24h]:[End time 24h], >=[Start time 24h]@row,
[Start time 24h]:[Start time 24h], <=[End time 24h]@row
)
)

Thank you!
Neil Egsgard

Neil Egsgard

@Paul Newcome , thanks for the feedback, I like the single date / time field.

The issue was the 25,000,000 referenced formula cell limit. A single sheet can only reference a total of 25,000,000 formula cells. I was referencing two columns with formulas which meant 8,000 cell X 8,000 = 64,000,000 referenced formula cells. More details on reference formula cell issue are in this discussion.

Genevieve P.

May 17, 2023

Hiya! Happy to jump in here.

The total count of cells referenced by formulas in a sheet cannot exceed 25 million, as you've found. This includes cross-sheet references, in-sheet cell references, column references, and range references.

However, I believe that only cells that contain formulas are counted towards the limit. What I mean by this is that if cell [Primary]1 is static, and cell [Column 2]1 has a formula like =[Primary]1 , then referred cell count is 0. But if a formula is added to  [Primary]1, that's when the referred cell count is 1.

Then if you have column references that are looking at an entire column (and that referenced column also has a formula), each individual cell is referencing the entire column of 2,824 cells (2,824formula cells x 2,824referenced cells = 7,974,976) as Paul noted.

This should help explain why your sheet wasn't hitting the limits before, but once you have overlapping formulas, the referenced count can shoot way up.

If you're having issues, you can reach out to Support for clarification about this one sheet, however they will likely advise you to reduce your formula count or simplify the formulas that are being used.

Cheers,

Genevieve

My solution was to use DataMesh to transfer the formula results to another sheet. The data on the new sheet will be values instead of formulas. I can now use either cross-sheet formulas or a DataMesh to bring the calculations back to the original sheet.

Paul Newcome

Have you tried a COUNTIFS instead? You could also create a string that is "YYYYMMDDhhmm" for the start and end and then just compare these columns to on a row by row basis so you are only evaluating two columns in your COUNTIFS instead of three.

=YEAR([Date Column]@row) + "" + RIGHT("0" + MONTH([Date Column]@row), 2) + RIGHT("0" + DAY([Date Column]@row), 2) + ………….

The rest of the above would be determined by the exact format of your time columns.

Neil Egsgard

@Paul Newcome , thanks for the feedback, I like the single date / time field.

The issue was the 25,000,000 referenced formula cell limit. A single sheet can only reference a total of 25,000,000 formula cells. I was referencing two columns with formulas which meant 8,000 cell X 8,000 = 64,000,000 referenced formula cells. More details on reference formula cell issue are in this discussion.

Genevieve P.

May 17, 2023

Hiya! Happy to jump in here.

The total count of cells referenced by formulas in a sheet cannot exceed 25 million, as you've found. This includes cross-sheet references, in-sheet cell references, column references, and range references.

However, I believe that only cells that contain formulas are counted towards the limit. What I mean by this is that if cell [Primary]1 is static, and cell [Column 2]1 has a formula like =[Primary]1 , then referred cell count is 0. But if a formula is added to  [Primary]1, that's when the referred cell count is 1.

Then if you have column references that are looking at an entire column (and that referenced column also has a formula), each individual cell is referencing the entire column of 2,824 cells (2,824formula cells x 2,824referenced cells = 7,974,976) as Paul noted.

This should help explain why your sheet wasn't hitting the limits before, but once you have overlapping formulas, the referenced count can shoot way up.

If you're having issues, you can reach out to Support for clarification about this one sheet, however they will likely advise you to reduce your formula count or simplify the formulas that are being used.

Cheers,

Genevieve

My solution was to use DataMesh to transfer the formula results to another sheet. The data on the new sheet will be values instead of formulas. I can now use either cross-sheet formulas or a DataMesh to bring the calculations back to the original sheet.

Paul Newcome

I thought when you said your sheet was "crashing" that it was timing out or something to that affect. I didn't realize you were getting an error message regarding the number of cells being referenced. But yes. That limitation can be a bit of a pain in larger sheets that need to run calculations. I'm hoping that gets increased soon.