When setRepeatFormulasWithSubtotal(true) the subtotal9 function gets a wrong result and grandtotal rows haven't be calculated

When setRepeatFormulasWithSubtotal(true) the subtotal9 function gets a wrong result, however when I don’t use this api (setsetRepeatFormulasWithSubtotal), subtotal result is correct, but the subtotal rows haven’t been calculated by “setRepeatFormulasWithSubtotal” int the “row total” column.
This is what I expect:
image.jpg (90.0 KB)
besides the subtotal for “2017,2018,2019” needed to be calculated the red box content is what I also want to calculate.

AsposeTest.zip (90.4 KB)
Above AsposeTest is a simple sample code, the ExpectedResult file is what I want to implement
the OutPut file is result when I setRepeatFormulasWithSubtotal(true)
the WithoutsetRepeatFormulasWithSubtotal file is result when I don’t use this api setRepeatFormulasWithSubtotal
the TestSmartMarkers file is template file.

At last, Again emphasizing : I want to get correct “Level1” subtotal result and also the subtotal rows, grandtotal rows can be calculated correctly by the “setRepeatFormulasWithSubtotal”. Hope your reply, thank you!

@kevinzhang,
We have analyzed the scenario here and have logged it in our database for further investigation. We will write back here once any update is ready to share.

This issue is logged as
CELLSJAVA-43209 - setRepeatFormulaWithSubtotal(true) not generating expected results while using SmartMarkers

@kevinzhang,

There are two issues:

  1. Subtotal formulas are lost.
    It’s a bug and we found it. We will fix it soon.

  2. Repeated formulas do not work.
    As we said in the other thread, please change the function SUBTOTAL as SUM because SUBTOTAL will ignore the cells whose formulas contain SUBTOTAL. Please check the attached template file “TestSmartMarkers_sum.xlsx” for your reference.
    files1.zip (7.4 KB)

Sorry to reply so late.
I have two questions:
1.There is no need to set F2 with ‘&=&=SUBTOTAL(9,C{r},D{r},E{r})’, row total “&=&=1” &=&=2 &=&=3 and subtotal9 row can be calculated. Because Other functions like AVERAGE will also calculate the subtotal row, so I need to use ‘&=&=SUBTOTAL(9,C{r},D{r},E{r})’ rather than ‘&=&=sum(C{r},D{r},E{r})’
To F4 cell, I need to manually to set the cell with “=SUM(C4:E4)”, right (as you say “SUBTOTAL will ignore the cells whose formulas contain SUBTOTAL” )? can’t it be extended automatically by F2 ‘&=&=SUBTOTAL(9,C{r},D{r},E{r})’ ?

2.I saw CELLSJAVA-43209 has been resolved, where can I get the latest version(jar) ?

This is my modified template:
TestSmartMarkers.xlsx.zip (8.9 KB)
is this is ok?

@kevinzhang,

For your queries,

  1. We will evaluate it and get back to you with details.
  2. Yes, the issue (logged earlier as “CELLSJAVA-43209”) is resolved and hopefully you will get the fix before the end of this week. We will share the fixed version here once available for public use.

Thank you very much. We have bought your license, I am the collegue of @xuanniao, have the same requirements (How to calculate the grandtotal for every row with expanded column? - #4 by ahsaniqbalsidiqui). Hope for your fixed version very much. If the fixed version is ready where can I get it or you will notify me here?

@kevinzhang,

We will share the download link for the fix in this thread once available.

@kevinzhang,

Please try our latest version/fix: Aspose.Cells for Java v20.6.2:
aspose-cells-20.6.2-java.zip (7.1 MB)

Let us know your feedback.

I can get correct results when I repalce ‘&=&=SUBTOTAL(9,C{r},D{r},E{r})’ with ‘&=&=sum(C{r},D{r},E{r})’ in F2 and F4 cell. As you said “SUBTOTAL will ignore the cells whose formulas contain SUBTOTAL”. Thank you, the subtotal rows can also be calculated.

@kevinzhang,
Good to know that your issue is sorted out. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

The issues you have found earlier (filed as CELLSJAVA-43209) have been fixed in Aspose.Cells for Java 20.7. This message was posted using Bugs notification tool by Amjad_Sahi