Problem with SUMPRODUCT

[vzczc]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

7.1.3

Spreadsheet application

Excel (Latest 365 version)

Description

When filling the formula property with a formula that uses SUMPRODUCT where one of the factors is an IF that refers to the current row, the if is preceeded by @ in the formula in Excel.

var doubleQuote = """";
sht.Cells[row, col].FormulaR1C1 = "IFERROR(SUMPRODUCT(IF(rngCurrency=RC1,1,0),rngLedgerDebit,rngDebitRate)/RC6),{doubleQuote})";

When opening this in Excel it looks like this. The problem is the @ before rngCurrency. If removed the formula calculates correctly
=IFERROR(SUMPRODUCT(IF(@rngCurrency=$A6;1;0);rngLedgerDebit;rngDebitRate)/$F6;"")

[vzczc]

I made a workaround to avoid the IF statement that handles this case that does not have this problem.
var doubleQuote = """";
sht.Cells[row, col].FormulaR1C1 = "IFERROR(SUMPRODUCT(--(rngCurrency=RC1),rngLedgerDebit,rngDebitRate)/RC6),{doubleQuote})";

[OssianEPPlus]

Thanks for your report! We're looking into this

[OssianEPPlus]

I was able to re-create your error and we are working on a fix. Do note however:

var doubleQuote = """";
sht.Cells[row, col].FormulaR1C1 = "IFERROR(SUMPRODUCT(IF(rngCurrency=RC1,1,0),rngLedgerDebit,rngDebitRate)/RC6),{doubleQuote})";

Appears to have an inaccurate formula. The number of parenthesis do not match. There's three ( and four ) The doublequotes also are not escaped properly at least not if using c#. I assume what you were going for given that the
=IFERROR(SUMPRODUCT(IF(@rngCurrency=$A6;1;0);rngLedgerDebit;rngDebitRate)/$F6;"") formula works correctly if the @ is removed
was something more like:

var doubleQuote = "\"\"";
sheet.Cells[row,col].FormulaR1C1 = $"IFERROR(SUMPRODUCT(IF(rngCurrency=RC1,1,0),rngLedgerDebit,rngDebitRate)/RC6,{doubleQuote})";

It would potentially help and speed up your case if you could provide a workbook wherein this happens. Also note that as a commercial customer can always log in to our website and use our support portal to upload files and support tickets privately.

[vzczc]

The doubleQuote issue was a result of copy/paste loosing this value, it is correct in the code. The same applies to the missing paren.

I will try to create an example for you, but am pressed for time today. I can upload an example in the support portal. There is also another problem when formulas like these are added, if I Calculate the workbook before saving, it takes a very long time. 15 seconds vs 1 if no calculation is done. It is probably easier for you to look at this when you have a test project that does this.

[OssianEPPlus]

Any update on this? We have not received a ticket yet.

[vzczc]

I have not had time to do this. As we found a way around it, it has not been a pressing issue. Will try to get back to you shortly with a test that illustrates the issue. Med vennlig hilsen Øystein Johnsen [SynteroLogoBasicNamed3.png] ***@***.******@***.***> +47 4800 5602 Kronprinsens gate 1 N-0251 Oslo www.syntero.no<http:https://www.syntero.no> From: OssianEPPlus ***@***.***> Sent: tirsdag 18. juni 2024 10:06 To: EPPlusSoftware/EPPlus ***@***.***> Cc: Øystein Johnsen ***@***.***>; Author ***@***.***> Subject: Re: [EPPlusSoftware/EPPlus] Problem with SUMPRODUCT (Issue #1468) Any update on this? We have not received a ticket yet. — Reply to this email directly, view it on GitHub<#1468 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AELJK347SWHT3NOXDOU6DITZH7S75AVCNFSM6AAAAABIMYCSKSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNZVGQ3DMOBSGY>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>

[OssianEPPlus]

No worries, just checking in as we cannot fully resolve the issue on our end without it.