Description: Numeric values in column D. Some of these number are deductible expenses. Column A has a flag to say which ones are deductible. This flag is a "y" (single char) when deductible else blank. Many lines are "y" and a few are blank. The answer 0 is definitely wrong for my data. This is the formula: =SUMIFS(D2:D61, A2:A61, "=1") Note that I imported this data from a CSV file. It is possible that some encoding problem has resulted in a situation where LibreOffice cannot see numbers as numbers. Visually, I cannot see a problem. Steps to Reproduce: Here is some sample data, use the formula above. Entry Date Desc Amt y 11/14/2019 abc 25 11/14/2019 def 10 Expected Results: 25 Reproducible: Always User Profile Reset: No Additional Info: The hint-help and online help for SUMIFS is particularly confusing. There are too many different opinions about the required parameters. I sanity checked my data and formula using Google Sheets. I pasted the data in and did the total using their syntax, and got a non-zero answer. Therefore I do think my data is clean and this is a core bug on a much needed feature.
Oops. Many formula attempts failed and I quickly pasted in the last one when filing this report. That obviously could not work on the tiny data sample which has no 1 there. I can assure you, these also fail: =SUMIFS(D2:D61, A2:A61, "y") =SUMIFS(D2:D61, A2:A61, "=y") =SUMIFS(D2:D61, A2:A61, ="y")
What if you attach the sample, so it can be tested.
Good suggestion to attach the sample, because, in doing that, and going through the data to sanitize it before uploading, I noticed that all my "numbers" were strings starting with ', due to something going wrong during the initial import. After removing all the leading ' symbols, SUMIFS worked to give a non-zero answer. Sorry to bother you. Perhaps a line could be added to the documentation re troubleshooting, if the Sum is 0, most likely the data is not numeric and has to be inspected at the cell level, not visually for the sheet.
Created attachment 158362 [details] SUMIFS with numeric data does not have problem
[Automated Action] NeedInfo-To-Unconfirmed
So there it's not a but. Calc it's strict about the use of string as number, and only works with a direct reference, e.g. =A1 works but =SUM(A1:A1) not. Some adjust can be done in Menu/Tools/Options/LibreOffice calc/Formula/Detailed calculation settings.