IF Function too long in Sheet Summary

Question

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”)))))))))))))))

TVang · Accepted Answer

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 · Accepted Answer

@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"))))))))))))