Metrics Sheet - Restrictions

SherryFox

I cannot for the life of me recall how I set up metrics sheets when I was at my previous company! My circumstance is that I have approx. 150 TDLs (they are like account #s), each with their own sheet in Smartsheet. And I need to count certain columns. They would be like doing a COUNTIF within Excel. The top screenshot is my Metrics Sheet, and the bottom is one of my TDL sheets. The 3 circled areas are the columns I need to count. These are my formula, I am selecting the entire column for each of these instances. Is there a more efficient way of doing this? As I am nowhere near done and I get this error (3rd screenshot).

Status (Green, Yellow & Red)

=COUNTIF({TDL-03 - Certify Access_Complete}, "Green")

Certify (Approve or Revoke)

=COUNTIF({TDL-03 - Certify Access_Certify}, "Approve")

Comments (one of 3 from the dropdown)

=COUNTIF({TDL-03 - Certify Access_Comments}, "User is Terminated")

jmyzk_cloudsmart_jp

Hi @SherryFox

If you're looking for an exact match only, use:

=COUNTIF({DL-03 : Certification Complete: sentence :20}, Color@row)

Cross-Sheet-Refereance 1

You can try this formula to count cells that contain the color in your row (e.g., "Green"):

=COUNTIF({DL-03 : Certification Complete: sentence :20}, CONTAINS(Color@row, @cell))

This counts the number of cells that contain the word (case-insensitive), not the number of times it appears within a single cell.

Cross-Sheet-Refereance 2

Note: If you want to count how many times the color (e.g., "Green") appears across the 20 cells — including multiple times in the same cell — you’ll need a more advanced setup using helper columns or external tools

SherryFox

Nevermind, I am using the Summary report, and I am copying to the other sheets, so I am good.

SherryFox

I do not like the way the Summary Report works for my Metrics. I have about 150 individual TDL sheets, and I need to count them based on the screenshots shown in my initial post. And I used the formulas shown. UNTIL I ran into the error (3rd screenshot). Please help!

jmyzk_cloudsmart_jp

Hi @SherryFox

Thanks for sharing the screenshots — your metrics sheet is super well organized, and I can see how powerful it is across 150+ TDL sheets!

Just a quick heads-up: the error you’re encountering in the last image might be due to hitting the Smartsheet formula cell reference limit — not just the number of formulas, but how many cells are being referenced.

Smartsheet's limit is 25 million referenced cells per sheet. This includes:

Cross-sheet references

Column formulas that reference other columns

Any formula referencing other formula-based cells

If you’re using cross-sheet formulas like:

=COUNTIF({TDL-03 - Certify Access_Certify}, "Approve")

…and each {} range points to an entire column (e.g., 3,000 rows), that counts 3,000 per reference — and it adds up fast when you multiply by the number of columns and sheets involved. Some setups could quickly reach millions of referenced cells, especially if those source columns also contain formulas.

https://community.smartsheet.com/discussion/105079/formula-cell-reference-limit-25-000-000-not-refreshing

SherryFox

@jmyzk_cloudsmart_jp,

Okay, that is what I want to avoid. How about referencing the range which is only 20 rows on each sheet. Is that doable? So that would 1:20. I completely forgot how to list a range like that!

jmyzk_cloudsmart_jp

@SherryFox

Yes, avoiding the column reference and limiting the reference to a specific number of rows will significantly reduce the number of references.

You can limit the range as follows;

=SUM([Row ID]1:[Row ID]20)

SherryFox

@jmyzk_cloudsmart_jp ,

In order to write this properly, I am writing this formula on my Metrics sheet, and then referencing the TDL sheet. For instance:

Sheet name: TDL-03 - Certify Access

Column Name: Certification Complete

Range: 1:20

Current Formula: =COUNTIF({TDL-03 - Certify Access_Complete}, "Green")

So how do I Do a formula. It must be a COUNTIF, as I am counting the times a specific word appears. For example, in the Certification Complete column, it could be Green, Yellow or Red. But the formula will need to count how many instances of each occur. SUM would not work.

jmyzk_cloudsmart_jp

Hi @SherryFox

If you're looking for an exact match only, use:

=COUNTIF({DL-03 : Certification Complete: sentence :20}, Color@row)

Cross-Sheet-Refereance 1

You can try this formula to count cells that contain the color in your row (e.g., "Green"):

=COUNTIF({DL-03 : Certification Complete: sentence :20}, CONTAINS(Color@row, @cell))

This counts the number of cells that contain the word (case-insensitive), not the number of times it appears within a single cell.

Cross-Sheet-Refereance 2

Note: If you want to count how many times the color (e.g., "Green") appears across the 20 cells — including multiple times in the same cell — you’ll need a more advanced setup using helper columns or external tools

SherryFox

Thanks @jmyzk