Cumulative Column Formula with Exclusion
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?
Answers
-
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?
-
Thank you, @Paul Newcome! I don't have a formula for the latest month yet.
-
Ok. I assume you are wanting to just pull the column to the immediate left then?
-
@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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!