# Cumulative Column Formula with Exclusion

Options

I have a cumulative column formula created that cumulatively adds up months, if we're in May, it will add Jan-April, if we're in June, it will add Jan-May, etc. There are some metrics the customer wants to be excluded from this formula and instead, have the formula provide the last month entered. I created a checkbox column, and I'm thinking if that checkbox is checked, the formula will not use the cumulative formula, but instead, pull the latest month entered (P01, P02, etc. in the screenshot). Any suggestions?

Tags:

• ✭✭✭✭✭✭
Options

Here is the basic idea...

=IF([Checkbox Column]@row <> 1, cumulative_formula, latest_month_formula)

Do you already have the formula for the latest month?

• Options

Thank you, @Paul Newcome! I don't have a formula for the latest month yet.

• ✭✭✭✭✭✭
Options

Ok. I assume you are wanting to just pull the column to the immediate left then?

• Options

@Paul Newcome The Cumulative Target and Cumulative Actuals columns have the current formula showing in the screenshot.

The Cumulative Target column uses the P01-P12 Target columns and Cumulative Actuals uses the P01-P12 Actual columns.

If the checkbox is checked, for the Cumulative Target column, I want to pull the last number entered in the P01-P12 Target column and for the Cumulative Actuals column, I want to pull the last number entered in the P01-P12 Actual column. Hope that makes sense. Thank you!

• ✭✭✭✭✭✭
Options

Ok. My suggestions...

Cumulative Target:

=SUM([P01 Target]@row:[P12 Target]@row)

Last Entered Target:

=INDEX([P01 Target]@row:[P12 Target]@row, 1, COUNTIFS([P01 Target]@row:[P12 Target]@row, @cell <> ""))

Target combined into a single formula based on box being checked:

=IF([Do Not Accumulate]@row <> 1, SUM([P01 Target]@row:[P12 Target]@row), INDEX([P01 Target]@row:[P12 Target]@row, 1, COUNTIFS([P01 Target]@row:[P12 Target]@row, @cell <> ""))

Cumulative Actual:

=SUM([P01 Actual]@row:[P12 Actual]@row)

Last Entered Actual:

=INDEX([P01 Actual]@row:[P12 Actual]@row, 1, COUNTIFS([P01 Actual]@row:[P12 Actual]@row, @cell <> ""))

Actual combined into a single formula based on box being checked:

=IF([Do Not Accumulate]@row <> 1, SUM([P01 Actual]@row:[P12 Actual]@row), INDEX([P01 Actual]@row:[P12 Actual]@row, 1, COUNTIFS([P01 Actual]@row:[P12 Actual]@row, @cell <> ""))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!