Skip to main content facebooktwittermenuarrow-up

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

0

Support decimals in SUMIF function Declined

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

Comments (2)

Mario at 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
Kamil
Hello,
This makes sense, thanks for explanation. From now on I will use double for calculations. However, it still may be confusing for some users who are not aware of this limitation, especially that decimal seems to work in most cases. Maybe it would make sense to disallow usage of decimals at all (unfortunately it would not be backward compatible) or always convert them to double under the hood? Anyway, these are just some loose thoughts. Thanks for the great tool :)

Regards,
Kamil