Count Collect formula crashing large sheet - Scheduling conflict identification
![Neil Egsgard](https://us.v-cdn.net/6031209/uploads/userpics/UZOH6EUN9H1P/nI9W1JLCLOG2U.jpg)
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
Best Answer
-
@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.
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.
Answers
-
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.
-
@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.
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!