Count Collect formula crashing large sheet - Scheduling conflict identification

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

Find more posts tagged with

Formulas

collect

Countif

Accepted answers

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.

https://community.smartsheet.com/discussion/comment/376183#Comment_376183

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.

All comments

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.

https://community.smartsheet.com/discussion/comment/376183#Comment_376183

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.

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.