Bug 154173 - =SUMIF(A$1:A$240,H1,B$1:B$240) does not work half the time
Summary: =SUMIF(A$1:A$240,H1,B$1:B$240) does not work half the time
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
7.5.1.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-03-13 21:21 UTC by Esam Salem
Modified: 2023-03-13 22:05 UTC (History)
0 users

See Also:
Crash report or crash signature:

Attachments
ods file (45.75 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-03-13 21:29 UTC, Esam Salem 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Esam Salem 2023-03-13 21:21:51 UTC 
Description:
=SUMIF(A$1:A$240,H1,B$1:B$240) does not work half the time

sumif fails to match content of H1 in equation above in A$1:A$240, although exists.

Actual Results:
results in 0

Expected Results:
a number > 0


Reproducible: Sometimes


User Profile Reset: No

Additional Info:
na
Comment 1 Esam Salem 2023-03-13 21:29:57 UTC 
Created attachment 185940 [details]
ods file

Look in Sheet "Processed" - Column L, H, and A
Comment 2 Eike Rathke 2023-03-13 22:04:55 UTC 
You have regular expressions enabled in your document, where * asterisk and other characters are operators. Switch off under Tools -> Options -> Calc -> Calculate, Formulas Wildcards. In your case even no wildcards. Or instead, use the formula

=SUMIF(A$1:A$240;"\Q"&H1&"\E";B$1:B$240)

for a quoted sequence.

See
https://help.libreoffice.org/7.5/en-GB/text/scalc/01/func_sumifs.html?DbPAR=CALC#bm_id658066580665806
https://help.libreoffice.org/7.5/en-GB/text/shared/optionen/01060500.html?&DbPAR=CALC#bm_id3154420