[SOLVED] SUMIF not working consistently

[Rhed]

Hi,

I've created a spreadsheet with two tabs to manage my supplements and provide my doctor with a condensed report. The first tab is the 173 lines of Data. The second tab is the Report. To create the Report tab, I sorted the Data and copied Data.B to Report.B as I wanted to ensure that the values would match for the sumif function. I then removed any duplicate lines from Report.B. In Report.A I placed the following SUMIF formula and pasted it into each field. The formula doesn't work on 41 lines but it does work on the rest of the 131 lines.

Code: Select all

=SUMIF(Data.$B$2:$B$173;B5;Data.$A$2:$A$173)

I don't have a typo because everything has been copy/paste. The 41 lines don't show an error, they just show 0.00 as the micrograms when there is data that should be summed and displayed instead of 0.00. I can't figure out why the formula works on 90 lines but not the others. So, for example, Report.A.8 has a value of 0.00 but it should be summing Data.A.5 with a value of 36666.67 and Data.A.6 with a value of 5357.14. Those fields actually contain a formula.

Code: Select all

=C5/D5*E5

and

Code: Select all

=C6/D6*E6

respectively.

The Data tab is often sorted by different columns but the Report should always sum the data regardless of it's position.

I'm using
AOO415m1(Build:9789) - Rev. 1817496
2017-12-11 17:25
on
Microsoft Windows 10 Home
Version 10.0.14393 N/A Build 14393

[FJCC]

I do not see any problem with your formula. Could yo upload the file, or a small part of it, so we can look at the same thing your are working with? To upload a file, click PostReply and look for the Upload Attachment tab just below the box where you type a response.

[Rhed]

@FJCC - Here's the attachment you requested.

[FJCC]

Go to the menu Tools -> Options, expand the OpenOffice Calc list on the left, select Calculate. Remove the check mark from Enable regular expressions in formulas. Your formula will then work.
All of the failing cases have asterisks or parentheses in the text of column B. These get interpreted as regular expressions, an advanced form of wild cards, and cause the problem.

[Zizi64]

The asterisk (*) _is_ a regular expression in the AOO/LO for the searching feature. You must switch off the Regular Expressions option, when you use the * character (or any other regular expression character) as a native character in your data.


From the LibreOffice Help:

SUMIF

Adds the cells specified by a given criteria. This function is used to browse a range when you search for a certain value.

The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters.

https://help.libreoffice.org/latest/en- ... _id3151957
https://help.libreoffice.org/latest/en- ... _id3146765

[Rhed]

Thanks FJCC!!!