SUMIF problem

users
#1

=SUMIF(D1:D14,"*Landline$",F1:F14)

Will this match any text in D1 to D14 that ends with the word "Landline"?

#2

no, because the criteria are wrong. But i dont know how to specify this the correct way, i only know a 'workaround'...

If you add cells G1..G14 with the forumula:
=RIGHT(Dx;8)="Landline" (replace 'x' with the rownumber)

Than this will return the sum of column F for the fields that match:
=SUMIF(G1:G14,TRUE(),F1:F14)

#3

John Meyer wrote:

=SUMIF(D1:D14,"*Landline$",F1:F14)

Will this match any text in D1 to D14 that ends with the word "Landline"?

I think you want:
   =SUMIF(D1:D14,".*Landline$",F1:F14)
(note the extra . before the *)

In regular expressions, "." matches any character, while "*" matches any number (including 0) of the previous expression. So "x*" would match any number of "x"s, while ".*" would match any number of any character. LibreOffice's help contains information on other regular expression syntax supported.

You also need to enable the option:
   Tools > Options > LibreOffice Calc > Calculate > Enable regular expressions in formulae
(otherwise SUMIF will look for the literal string ".*Landline$")

Mark.

#4

Hmm, this looks good, i did not know of the regular expression setting....

But if you do not want MyLandline in the result, you should also add a space :wink:

".* Landline$"