Auto calculate last 12 columns

[TB_Mylene]

Hi All, I need assistance with a workflow that auto calculates the last 12 columns every month. I have a report with full year results by months but I only need to sum the last 12 months. Every month a new column is added. 

For example, I need to sum columns K through V for this month. Next month I need to sum columns L though W. I've attached an sample file.

Any advice is much appreciated. 

Thanks!

[Qiu · Accepted solution]

@TB_Mylene 
We can do a Transpose first, then order the columns in a decending order and select the top 12 months.

[flying008]

Hi, @TB_Mylene 

Another way is a dynamic solution for you :

******

@Qiu  
I have discovered an unimaginable phenomenon, There are 3 fields like [A] [B] [C], if you want to [C] = [A] + [B] , now [A] = 3, but value of [B] is null, then the [C] get a null result after run !  What's wrong ?  (this is reason of must use Multi-Field Formula tool in above workflow.)

[TB_Mylene]

Thank you so much! I think this may work. I will try this out and let you know. 

[Qiu]

@flying008 
I am always not quite get the "Dynamic Replace". Thanks  for great sample.

And you are right.
At least in the Formula tool if you add one numeric valve to a Null value, the result will be Null as well.

But somehow it works for the Summarize tool but We shoul definitely add one Cleasing tool to be safe.

• Replace null values with blanks in the case of text fields and change nulls to zeros in the case of numeric ones

---

[flying008]

Hi, @Qiu 

Thank you for your reply !  IMHO, 1 + Null() = 1, but the formula get result is Null, so what is the reason (even in v2023.1)?

[TB_Mylene]

@Qiu  your suggestion worked within my workflow. Thank you again!