Problem with the sumif function

users
#1

I had this working, then I noticed that it is no longer working (no error messages).

Sums any cells in col F if there is a 2211 2212 or 2213 in col M:

=SUMIF(M6:M351,"2211|2212|2213",F6:F351)

it now gives a result of zero.

I tried changing the format of the searched col to text or general number, no change

I have set the justification to 'centered'.  Since the title is greater that the width of the 4 characters,

there is a space before and after the 4 characters, but I think this should be for display only, name changing the data.

Turning off centered justification, etc. makes no difference.

Help?

John

#2

That use of the "|" character to mean "or" means that your criterion is specified by means of a regular expression. For this to work, you need to have this facility enabled at Tools | Options... | LibreOffice | Calc | Calculate | Enable regular expressions in formulas. Without this, the function will match only the text interpreted as a single fourteen-character string.

I'm guessing, but do you have this option disabled?

I trust this helps.

Brian Barker

#3

I had this working, then I noticed that it is no longer working (no error
messages).

Sums any cells in col F if there is a 2211 2212 or 2213 in col M:

=SUMIF(M6:M351,"2211|2212|2213",F6:F351)

Are you sure this is supposed to work? I can't find any examples like this.

However, this works:
=SUMIF(M6:M351,2211,F6:F351) + SUMIF(M6:M351,2212,F6:F351) +
SUMIF(M6:M351,2213,F6:F351)

This also works:
{=SUM(SUMIFS(F6:F351;M6:M351;{2211;2212;2213}))}
(it's a matrix formula, so enter everything except the surrounding {}, then
hit Ctrl+Shift+Enter rather than only Enter)

Kind regards

Johnny Rosenberg

#4

I had this working, then I noticed that it is no longer working (no error
messages).

Sums any cells in col F if there is a 2211 2212 or 2213 in col M:

=SUMIF(M6:M351,"2211|2212|2213",F6:F351)

Are you sure this is supposed to work? I can't find any examples like this.

However, this works:
=SUMIF(M6:M351,2211,F6:F351) + SUMIF(M6:M351,2212,F6:F351) +
SUMIF(M6:M351,2213,F6:F351)

This also works:
{=SUM(SUMIFS(F6:F351;M6:M351;{2211;2212;2213}))}
(it's a matrix formula, so enter everything except the surrounding {},
then hit Ctrl+Shift+Enter rather than only Enter)

Oops… I meant it's an ARRAY formula. It's ”matrisformel” in my language, so
I accidently translated ”matris” to ”matrix”, which seems like a good idea,
but there are a lot of words that look like they mean something while they
really mean something quite different… for instance, my language's word
”semester” means vacation and there are a few more examples of words that
also likely could cause some confusion… :stuck_out_tongue:

Kind regards

Johnny Rosenberg

#5

thank you both.  I checked regular expressions, and it immediately worked.  The interesting part is I would not have "unchecked" it, and it previously worked.  I wonder if there was an upgrade issue, but as for me, now, "IT WORKS!"

thank you'

john