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?
