Sheet summary formula becomes #unparseable upon sheet exit

Mlichtenstein

Hey folks, I'm hoping you can help me solve a mystery. I have about 30 sheet summary fields all using the same formula, which counts two types of responses in each column. I copied and pasted the basic formula in every sheet summary field, then changed to the appropriate column name (the column names are spelled correctly). The mystery is, every time I save and exit the sheet, the brackets around the column names for 2 sets of the summary fields disappear, and then those formulas are #unparseable. Here are some screenshots and my formulas: Screenshot one: taken upon a reload of the sheet, the brackets around the Industry Agreement column name have disappeared. Screenshot two: shows the brackets around the Occupation Agreement column name have NOT disappeared.

Here are the two formulas I use before saving the sheet:

Industry: =COUNTIF([Industry Agreement]:[Industry Agreement], "Agree") + (COUNTIF([Industry Agreement]:[Industry Agreement], "Can live with"))

Occupation: =COUNTIF([Occupation Agreement]:[Occupation Agreement], "Agree") + (COUNTIF([Occupation Agreement]:[Occupation Agreement], "Can live with it"))

This is also happening to our column about Disability data, which is titled "Disability Agreement". I can't figure out why the terms Industry and Disability are related to the brackets disappearing... Thanks in advance!

Carson Penticuff

My first thought was that there was an error in parsing. If you place square brackets around a column label that does not need them, the brackets are removed automatically. This does not appear to be the case in your example. I tried to replicate this behavior, but creating a summary field with the same name and formula along with a column with the same name and entries does not produce the same results when saving, leaving the document, and then opening the same document again.

A couple of troubleshooting steps you could try:

• Delete the sheet summary field and recreate it
• Temporarily change the formula to reference a specific range instead of the entire column. I.E., [Industry Agreement]1:[Industry Agreement]10
• Try using COUNTIFS() instead of COUNTIF(). Even though you are only evaluating one condition, COUNTIFS() will function the same.
• Temporarily rename the Industry Agreement column.

As a side note, I noticed the entries in the sheet are "Can live with it", while you use "Can live with" in your formula for Industry Agreement in the sheet summary, meaning your results would not be correct.

Mlichtenstein

Thanks Carson - renaming the column was the only solution that worked, even when i changed it back to the original name. Still a mystery, but I'm glad my formulas are working. And thanks for the eagle eyes on my mistake!

Carson Penticuff

That is very bizarre, but at least it is working now.