Skip to main content facebooktwittermenuarrow-up

Support decimals in SUMIF function - Community / GemBox.Spreadsheet Feature Request - GemBox Support Center

Hello!
The SUMIF functions seems to work well when working with ints and doubles, but not with decimals. Example:

ExcelFile ef = new ExcelFile();
ExcelWorksheet ws = ef.Worksheets.Add("Formula");

// this would work with integer or double values here
ws.Cells["A1"].Value = 300.15M;
ws.Cells["A2"].Value = 200.20M;
ws.Cells["B1"].Value = true;
ws.Cells["B2"].Value = false;

ws.Cells["C1"].Formula = "=SUMIF(B:B,TRUE,A:A)";

ws.Calculate();

// should print 300
Console.WriteLine(ws.Cells["C1"].StringValue);

It would be great if this would work also with decimals. I'm mostly using decimals for my calculations and everything else seems to work well, besides SUMIF function. For example, SUM works well.

Thanks,
Kamil

Comment (1)

Mario - GemBox
Hi,

Note, the problem is that Excel files don't support decimal values.

You see, the number precision in XLSX format is 15 digits, please check the "Calculation specifications and limits" section on the following page:
https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

So when saving such an ExcelFile object, the value will be converted to double.
That is why we feel there is no reason to use the decimal in the first place, the roundtrip won't work with it because the value will be rounded to 15 digits.

I hope this makes sense.

Regards,
Mario

Add a comment

Please log in or register to submit a comment.

Need a password reminder?