Sheetgo Forms configurations help you collect the right data. Formulas take that a step further: the form itself performs calculations while the person fills it out, using familiar spreadsheet functions and mathematical operators. Totals, ratios, and counts arrive in your Google Sheets file already computed.
Supported functions
You can use these basic spreadsheet functions in a formula:
SUM adds values.
AVERAGE calculates the mean.
COUNT counts numeric fields and excludes text.
COUNTA counts non-empty fields.
MAX finds the highest value.
MIN finds the lowest value.
You can also use the mathematical operators + (addition), - (subtraction), * (multiplication), and / (division), and group operations with parentheses.
MAX and MIN here are spreadsheet functions that run inside a formula expression. They're separate from the min and max configurations, which are validation settings covered in "Configure your Sheetgo Forms".
Write a formula
To create a dynamically calculated field, add the formula configuration to a question's header, followed by the expression you want to calculate.
To refer to another question, write its header text in single or double quotes. Use only the header text and leave out the bracketed configurations. If the header in your spreadsheet reads Price per order [required], the formula refers to it as "Price per order".
Only the question names take quotes. Everything else in the expression (functions, operators, numbers, and parentheses) stays outside the quotes.
Example expressions
Here are some expressions you can use:
[formula: "headerTitle1" + "headerTitle2"]
[formula: "headerTitle1" - "headerTitle2"]
[formula: "headerTitle1" * "headerTitle2"]
[formula: "headerTitle1" / "headerTitle2"]
[formula: ("headerTitle1" / "headerTitle2") * 100]
[formula: ("headerTitle1" * "headerTitle2") / "headerTitle3"]
[formula: SUM("headerTitle1", "headerTitle2")]
[formula: COUNT("headerTitle1", "headerTitle2", "headerTitle3")]
[formula: SUM("headerTitle1", "headerTitle2", "headerTitle3") / "headerTitle1"]
Purchase order example
Formulas fit naturally in a purchase order form, where you have fields for the price and the quantity of an item.
In the spreadsheet, the Total Cost question calculates its value by multiplying the answer to Price per order by the answer to Amount of orders:
Total Cost [formula: "Price per order" * "Amount of orders", locked]
When someone fills out the form, the Total Cost field shows the calculated result.
Add locked so no one edits the calculated result. Without locked, the field stays editable, and the person filling out the form can overwrite the calculated value.
Error messages
When a formula fails, the field shows the same error codes a spreadsheet would, so people familiar with spreadsheets can troubleshoot their forms easily.
#VALUE!the formula received invalid values.#NAME!the expression uses an unsupported or misspelled function name.#REF!the formula refers to its own field.
Formula showing an error? Open the chat in the bottom-right corner of this page and send us the exact header text. We'll take a look.
Related articles