Cumulative Column Formula with Exclusion

Alyssa Steinruck

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?

Paul Newcome

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?

Alyssa Steinruck

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

Paul Newcome

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

Alyssa Steinruck

@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!

Paul Newcome

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