IF Function too long in Sheet Summary

terrc046

Hi, I have an IF statement that is too long to add to the Sheet Summary Field. Is there a way I can shorten it? Please see below. (I've already created one that with less criteria that works, but want to add one more criteria to the formula.)

=IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 0, [Learning Done]4 = 0, [Learning Done]5 = 0, [Learning Done]6 = 0, [Learning Done]7 = 0, [Learning Done]8 = 0, [Learning Done]9 = 0, [Learning Done]10 = 0, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), [Product Learnings]2, IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 0, [Learning Done]5 = 0, [Learning Done]6 = 0, [Learning Done]7 = 0, [Learning Done]8 = 0, [Learning Done]9 = 0, [Learning Done]10 = 0, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), JOIN([Product Learnings]2:[Product Learnings]3, ", "), IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 0, [Learning Done]6 = 0, [Learning Done]7 = 0, [Learning Done]8 = 0, [Learning Done]9 = 0, [Learning Done]10 = 0, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), JOIN([Product Learnings]2:[Product Learnings]4, ", "), IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 0, [Learning Done]7 = 0, [Learning Done]8 = 0, [Learning Done]9 = 0, [Learning Done]10 = 0, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), “Not Started”, IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 0, [Learning Done]8 = 0, [Learning Done]9 = 0, [Learning Done]10 = 0, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), [Product Learnings]6, IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 0, [Learning Done]9 = 0, [Learning Done]10 = 0, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), JOIN([Product Learnings]6:[Product Learnings]7, ", "), IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 0, [Learning Done]10 = 0, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), JOIN([Product Learnings]6:[Product Learnings]8, ", "), IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 1, [Learning Done]10 = 0, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), “Not Started”, IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 1, [Learning Done]10 = 1, [Learning Done]11 = 0, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), [Product Learnings]10, IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 1, [Learning Done]10 = 1, [Learning Done]11 = 1, [Learning Done]12 = 0, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), JOIN([Product Learnings]10:[Product Learnings]11, ", "), IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 1, [Learning Done]10 = 1, [Learning Done]11 = 1, [Learning Done]12 = 1, [Learning Done]13 = 0, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), JOIN([Product Learnings]10:[Product Learnings]12, ", "), IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 1, [Learning Done]10 = 1, [Learning Done]11 = 1, [Learning Done]12 = 1, [Learning Done]13 = 1, [Learning Done]14 = 0, [Learning Done]15 = 0, [Learning Done]16 = 0), “Not Started”, IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 1, [Learning Done]10 = 1, [Learning Done]11 = 1, [Learning Done]12 = 1, [Learning Done]13 = 1, [Learning Done]14 = 1, [Learning Done]15 = 0, [Learning Done]16 = 0), [Product Learnings]14, IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 1, [Learning Done]10 = 1, [Learning Done]11 = 1, [Learning Done]12 = 1, [Learning Done]13 = 1, [Learning Done]14 = 1, [Learning Done]15 = 1, [Learning Done]16 = 0), JOIN([Product Learnings]14:[Product Learnings]15, ", "), IF(AND([Learning Done]1 = 1, [Learning Done]2 = 1, [Learning Done]3 = 1, [Learning Done]4 = 1, [Learning Done]5 = 1, [Learning Done]6 = 1, [Learning Done]7 = 1, [Learning Done]8 = 1, [Learning Done]9 = 1, [Learning Done]10 = 1, [Learning Done]11 = 1, [Learning Done]12 = 1, [Learning Done]13 = 1, [Learning Done]14 = 1, [Learning Done]15 = 1, [Learning Done]16 = 1), JOIN([Product Learnings]14:[Product Learnings]16, ", "), "Not Started”)))))))))))))))

Toufong Vang

The two logical expressions--using AND() and using JOIN()="some_string"--are functionally equivalent. You have 16 options/checkboxes so, most of the time, your IF() formula will evaluate to "Not Started" if 1-to-16 boxes can be checked at anytime.

That said, to make it easier to evaluate New Quarter Started, you'll want a way to differentiate these rows from the Example X rows.

One way to do this is to "indent" the Example X rows, making them child rows to the parent rows, New Quarter Started. Doing this avails you Smartsheet's hierarchy functions. For example, a revised formula would evaluate the immediate child rows when New Quarter Started is checked.

terrc046

@Toufong Vang , utilizing the parent/child rows as you said solved it and allowed me to shorten the formula so that it fits (see below), thank you! I had to use COUNTIF() in order to use CHILDREN(), but I didn't have to include every single cell in the formula!

=IF(AND([Learning Done]1 = 1, COUNTIF(CHILDREN([Learning Done]1), 1) = 1, [Learning Done]5 = 0, [Learning Done]9 = 0, [Learning Done]13 = 0, COUNTIF(CHILDREN([Learning Done]5), 1) = 0, COUNTIF(CHILDREN([Learning Done]9), 1) = 0, COUNTIF(CHILDREN([Learning Done]13), 1) = 0), [Product Learnings]2, IF(AND([Learning Done]1 = 1, COUNTIF(CHILDREN([Learning Done]1), 1) = 2, [Learning Done]5 = 0, [Learning Done]9 = 0, [Learning Done]13 = 0, COUNTIF(CHILDREN([Learning Done]5), 1) = 0, COUNTIF(CHILDREN([Learning Done]9), 1) = 0, COUNTIF(CHILDREN([Learning Done]13), 1) = 0), JOIN([Product Learnings]2:[Product Learnings]3, ", "), IF(AND([Learning Done]1 = 1, COUNTIF(CHILDREN([Learning Done]1), 1) = 3, [Learning Done]5 = 0, [Learning Done]9 = 0, [Learning Done]13 = 0, COUNTIF(CHILDREN([Learning Done]5), 1) = 0, COUNTIF(CHILDREN([Learning Done]9), 1) = 0, COUNTIF(CHILDREN([Learning Done]13), 1) = 0), JOIN([Product Learnings]2:[Product Learnings]4, ", "), IF(AND([Learning Done]1 = 1, COUNTIF(CHILDREN([Learning Done]1), 1) = 3, [Learning Done]5 = 1, [Learning Done]9 = 0, [Learning Done]13 = 0, COUNTIF(CHILDREN([Learning Done]5), 1) = 1, COUNTIF(CHILDREN([Learning Done]9), 1) = 0, COUNTIF(CHILDREN([Learning Done]13), 1) = 0), [Product Learnings]6..., "Not Started"))))))))))))

Toufong Vang

Hi, @terrc046, you can shorten your formula by joining the values in the "Learning Done" column then evaluate the result of the JOIN(), instead of individually evaluating each cell value with the AND() function. For example,

IF( JOIN([Learning Done]1:[Learning Done]16)="11000000000000", [Product Learnings]2, IF( JOIN([Learning Done]1:[Learning Done]16)="11100000000000", JOIN([Product Learnings]2:[Product Learnings]3, ", ")...etc.

terrc046

Hi @Toufong Vang , thank you for replying! Unfortunately, that didn't work for me. It didn't change it's result when I made changes to the [Learning Done] cells (checked the boxes) like I need it to; it just stays on "Not Started" as if none of the criteria was met to produce a different result.

I've attached a screenshot to reference what I'm trying to do. In a Sheet Summary, I'm trying to return what is in the [Product Learnings] column when certain criteria are met in the [Learning Done] column or return "Not Started." For example, when the boxes next to the cells that say "New Quarter Started" are checked and the ones below them are not checked, I want it to say "Not Started." But as I check the boxes below them, the sheet summary field will return the names in the three cells below it (so as I check the boxes next to Examples 1-3, it will go from saying, "Example 1," to saying, "Example 1, Example 2" to "Example 1, Example 2, Example 3"). It would then start over with "Not Started" when the next "New Quarter Started" is checked and the cells below it are not. This is so that it can automatically update to the correct information on my dashboard each quarter when I update this sheet.

I believe I have the correct formula in my question above as it worked for me when I excluded some cells, but now it is too long when I include all the cells and criteria I need.

Toufong Vang

The two logical expressions--using AND() and using JOIN()="some_string"--are functionally equivalent. You have 16 options/checkboxes so, most of the time, your IF() formula will evaluate to "Not Started" if 1-to-16 boxes can be checked at anytime.

That said, to make it easier to evaluate New Quarter Started, you'll want a way to differentiate these rows from the Example X rows.

One way to do this is to "indent" the Example X rows, making them child rows to the parent rows, New Quarter Started. Doing this avails you Smartsheet's hierarchy functions. For example, a revised formula would evaluate the immediate child rows when New Quarter Started is checked.

terrc046

@Toufong Vang , utilizing the parent/child rows as you said solved it and allowed me to shorten the formula so that it fits (see below), thank you! I had to use COUNTIF() in order to use CHILDREN(), but I didn't have to include every single cell in the formula!

=IF(AND([Learning Done]1 = 1, COUNTIF(CHILDREN([Learning Done]1), 1) = 1, [Learning Done]5 = 0, [Learning Done]9 = 0, [Learning Done]13 = 0, COUNTIF(CHILDREN([Learning Done]5), 1) = 0, COUNTIF(CHILDREN([Learning Done]9), 1) = 0, COUNTIF(CHILDREN([Learning Done]13), 1) = 0), [Product Learnings]2, IF(AND([Learning Done]1 = 1, COUNTIF(CHILDREN([Learning Done]1), 1) = 2, [Learning Done]5 = 0, [Learning Done]9 = 0, [Learning Done]13 = 0, COUNTIF(CHILDREN([Learning Done]5), 1) = 0, COUNTIF(CHILDREN([Learning Done]9), 1) = 0, COUNTIF(CHILDREN([Learning Done]13), 1) = 0), JOIN([Product Learnings]2:[Product Learnings]3, ", "), IF(AND([Learning Done]1 = 1, COUNTIF(CHILDREN([Learning Done]1), 1) = 3, [Learning Done]5 = 0, [Learning Done]9 = 0, [Learning Done]13 = 0, COUNTIF(CHILDREN([Learning Done]5), 1) = 0, COUNTIF(CHILDREN([Learning Done]9), 1) = 0, COUNTIF(CHILDREN([Learning Done]13), 1) = 0), JOIN([Product Learnings]2:[Product Learnings]4, ", "), IF(AND([Learning Done]1 = 1, COUNTIF(CHILDREN([Learning Done]1), 1) = 3, [Learning Done]5 = 1, [Learning Done]9 = 0, [Learning Done]13 = 0, COUNTIF(CHILDREN([Learning Done]5), 1) = 1, COUNTIF(CHILDREN([Learning Done]9), 1) = 0, COUNTIF(CHILDREN([Learning Done]13), 1) = 0), [Product Learnings]6..., "Not Started"))))))))))))