Need assistance writing a formula for one column with two purposes

Brooks

Hello!

I am creating a budget sheet and I would like to have a formula that calculates BOTH:

1) SUM across several cells in a row when box is not checked AND

2) SUM down a column if box is checked.

So far, I have been able to write a formula that calculates the SUM across several cells (and have added in a bit to return a blank cell when the box is checked).

Here is an example formula so far (for "Summer: Expenses):

=IF([Pay Period]@row = 0, (SUM([Summer 1]@row:[Summer 4]@row)))

Can you assist me is altering this formula to calculate both across a row and then down a column? I have attached a sample table.

Thanks for your help!

Genevieve P.

Hi @Brooks

Thank you for clarifying! In this case, no, you won't be able to put the formula in the same column you're looking to SUM all together, it will create a circular reference error even though the False statement may not be output in the other rows.

I would suggest having two formulas: one total SUM for the Top row and the other SUM for the rest of the rows:

=SUM([Summer: Expenses]:[Summer: Expenses])

^You can reference the entire column once if the formula is in the same column it's referencing, so you could have it in the top row.

=SUM([Summer 1]@row:[Summer 4]@row)

Formulas will auto-fill as long as there are 2 rows above that contain the formula, so if you drag-fill the formula down your sheet then new rows will update with the second formula. See: Use or Override Automatic Formatting and Formula Autofill

Cheers,

Genevieve

Genevieve P.

Hi @Brooks

IF statements first go through what to do IF the statement is true:

=IF([Pay Period]@row = 0, SUMacrossRows

Then you can say what to do IF that first statement is "False", or = 1

=IF([Pay Period]@row = 0, SUMacrossRows, SUMDownAColumn)

For example:

=IF([Pay Period]@row = 0, SUM([Summer 1]@row:[Summer 4]@row), SUM([Summer Expenses]:[Summer Expenses]))

Let me know if this is what you were looking to do!

Cheers,

Genevieve

Brooks

Hi @Genevieve P.

Thanks for your response! I tried what you wrote, but it wasn't quite right according to the Smartsheet. It returns a syntax problem.

I didn't mention last time where the formula was going, which might be part of the problem. I would like the formula to be a column formula for the Summer: Expenses column. So, it will SUM down a column if the pay period box is checked (the orange cell in that column). It will SUM across the row when pay period is not checked.

Does the location of the formula make a difference here? Thanks for your assistance!

Genevieve P.

Hi @Brooks

Thank you for clarifying! In this case, no, you won't be able to put the formula in the same column you're looking to SUM all together, it will create a circular reference error even though the False statement may not be output in the other rows.

I would suggest having two formulas: one total SUM for the Top row and the other SUM for the rest of the rows:

=SUM([Summer: Expenses]:[Summer: Expenses])

^You can reference the entire column once if the formula is in the same column it's referencing, so you could have it in the top row.

=SUM([Summer 1]@row:[Summer 4]@row)

Formulas will auto-fill as long as there are 2 rows above that contain the formula, so if you drag-fill the formula down your sheet then new rows will update with the second formula. See: Use or Override Automatic Formatting and Formula Autofill

Cheers,

Genevieve

Brooks

Thanks, @Genevieve P. ! Your explanation was much appreciated 😊