Trouble Referencing Same-Sheet Cell in a Logical Expression (IF or OR)

Options

Hi all,

I know this has been asked and answered a number of times, but none of the solutions previously given have worked for me. Maybe my formula is special...I dunno.

I have an expense planning sheet, and my client wants to be able to include options (option 1, Option 2, etc.) for some line items, and a way to roll-up costs by each option, omitting others.

Since filters don't remove a hidden row from the roll-up sums, I had to go about it a different way; I'm using a cell in row 1 (on the same sheet) as a picker that will inform the Projected Cost formula which options to omit from calculation.

I cannot get it to work, and I've tried about 10 variations at this point.

In the formula that I've landed on;

=IF(Options@row = "", IF(COUNT(CHILDREN([Item or Service]@row)) = 0, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), SUM(CHILDREN())), IF(Options@row = [Account Code]1, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), 0))



The trouble lies in the bolded piece of the formula. No matter how I try to get around it, it just throws a syntax error:

However, if I change [Account Code]1 to just a 1 (or 2 or 3), the formula works perfectly:


Could it be that the ColumnA@row format doesn't play nice with the in-sheet reference, but ColumnA1 does?

Does anyone see a way to make this work?

Best Answer

  • Kevin M.
    Kevin M. ✭✭✭✭
    Answer ✓
    Options

    Coming back to report that I have found a workaround.

    I moved the "options-to-factor" selector from [Account Code]1 and placed it as a field in the Sheet Summary, then modified the formula accordingly.

    =IF(Options@row = "", IF(COUNT(CHILDREN([Item or Service]@row)) = 0, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), SUM(CHILDREN())), IF(Options@row = [Option Selector]#, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), 0))

    This is working well, but I'm still curious about the earlier predicament. I can imagine some instances where I might run into the same issue.


    -Kevin

Answers

  • Kevin M.
    Kevin M. ✭✭✭✭
    Answer ✓
    Options

    Coming back to report that I have found a workaround.

    I moved the "options-to-factor" selector from [Account Code]1 and placed it as a field in the Sheet Summary, then modified the formula accordingly.

    =IF(Options@row = "", IF(COUNT(CHILDREN([Item or Service]@row)) = 0, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), SUM(CHILDREN())), IF(Options@row = [Option Selector]#, SUM(([Price/Rate]@row * [Unit Quantity]@row), Tax@row, Shipping@row), 0))

    This is working well, but I'm still curious about the earlier predicament. I can imagine some instances where I might run into the same issue.


    -Kevin

  • Marcela
    Marcela Employee Admin
    Options

    Hey @Kevin M.

    Glad you worked it out! Thank you for posting your solution!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!