There are three options for easily adding a comment or note within an array, non-array, and lambda formula in Google Sheets: Using N, LET, and IFERROR functions.

Suppose you have several formulas in your sheet. Even if you are an expert, it’s always better to leave a brief description of the formula within it. This will help you (or others) understand it in the future.

Here’s how to add a comment, note, or brief description (whichever you prefer) inside a formula in Google Sheets:

Adding Comments Inside Non-Array Formulas

To find the total purchase expenses, use the following formula when you have the purchase quantities in D2:D and the prices in E2:E.

=SUMPRODUCT(D2:D, E2:E)

You can add a comment within this SUMPRODUCT formula in three ways:

Option 1

=SUMPRODUCT(D2:D, E2:E) + N("This formula returns the total purchase cost")

Here we have used the N function to add the comment.

When the argument is text, this function returns 0, so adding 0 to your SUMPRODUCT doesn’t affect your output.

The comment is what you see inside the N function.

Note:

If the original formula returns a text output, you should replace the + operator with the & operator. Additionally, wrap the N function with the T function.

Example: & T(N("This formula calculates the result for each item in the entire column"))

The N function returns 0, and when the argument in the T function is a number (0 in this case), it returns an empty string.

These points are also applicable to the array formula and lambda functions below, where we use the N function.

Option 2

=SUMPRODUCT(D2:D, E2:E) + IFERROR("This formula returns the total purchase cost"/0)

Here we follow a similar logic.

When you divide text by a number, the formula will return a #VALUE error. We have used IFERROR to remove that error, resulting in an empty cell. Adding this output to the original formula doesn’t impact the result.

When dealing with a formula that returns text output, you should replace the + operator with the & operator. This applies to the array formula and lambda functions below, where we use the IFERROR function to add comments.

Option 3

=LET(comment, "This formula returns the total purchase cost", SUMPRODUCT(D2:D, E2:E))

Here the approach is quite different.

The LET function helps assign names to value expressions and returns the result of the formula expression.

Without the syntax of the LET function, you won’t be able to read the above formula:

Syntax:

Syntax: LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

Where:

name1: comment
value_expression1: “This formula returns the total purchase cost” (add the comments you want)
formula_expression: SUMPRODUCT(D2:D, E2:E)

Adding Comments Inside Array Formulas

When adding comments within an array formula, it is very important where you place the comment. Otherwise, the formula may leave trailing zeros.

The following array formula in cell F2 will return the amount of each item in rows wherever the item name is present in A2:A.

=ArrayFormula(IF(A2:A="",, D2:D*E2:E))

In this formula, you should add the notes to D2:D*E2:E when using the N and IFERROR functions. Here they are:

Using N Function:

=ArrayFormula(IF(A2:A="",, D2:D*E2:E + N("This formula calculates the result for each item in the entire column")))

Using IFERROR function:

=ArrayFormula(IF(A2:A="",, D2:D*E2:E + IFERROR("This formula calculates the result for each item in the entire column"/0)))

When using LET, you can follow the same approach that we adopted with the non-array formula.

Using LET function:

=ArrayFormula(LET(comment, "This formula calculates the result for each item in the entire column", IF(A2:A="",, D2:D*E2:E)))

Embedding Comments Within Lambda Helper Functions

MAP, REDUCE, BYROW, BYCOL, SCAN, and MAKEARRAY are the lambda helper functions (LHFs) in Google Sheets.

When adding comments within the lambda helper functions, add them to the formula expression section. Let’s use the SCAN LHF for the test.

In our sample data, to get the cumulative sum of amounts, you can use the following formula in cell G2:

=SCAN(0, F2:F11, LAMBDA(a, v, a+v))

Here is how to add a comment to this formula:

=SCAN(0, F2:F11, LAMBDA(a, v, a+v + N("Running Sum")))
=SCAN(0, F2:F11, LAMBDA(a, v, a+v + IFERROR("Running Sum"/0)))
=LET(comment, "Running Sum", SCAN(0, F2:F11, LAMBDA(a, v, a+v )))

Conclusion

Out of the three methods, I suggest using the N function to add comments within formulas in Google Sheets. This method is straightforward and minimally intrusive, making it an efficient way to embed comments directly within your formula expressions.

Unlike the IFERROR method, which relies on generating and then handling an error, the N function is simple and effective, leveraging its ability to convert text to 0 without affecting the formula’s output.

The LET function is more suitable for complex formulas where you want to define and reuse variables. For simple commenting, it can be seen as overkill.