Exclude cells with certain keywords from SUMIFS

[Maykay]

Hi.

I would like to exclude certain cells from being counted by a SUMIFS function. The cells don’t all have the same text but they have a keyword in common (in this case it’s “1/2”). I’d also like to futureproof it, so I’d like to use a general purpose formula and not just exclude current, specific cells (as more will be added in the future that also contain the keyword).

I know how to exclude cells with a specific text but I can’t figure out how to exclude cells which have a particular word (for instance, in Windows Explorer you could search for “* tree *” and it would give you all files and folders that have the word “tree” anywhere in their name). It should be the same in Calc, and I think it works with pure text, but could it be that, since the ‘word’ I’m excluding is actually a number, it’s not working?

Examples of cells I want to exclude from being counted: “AAA 1/2”, “DDD 1/2”, etc.

So in this simple matrix:

AAA ½	1
AAA	1
B	3
C	2
DDD ½	2
D	1

I would like to count everything in column B if the cell in column A does not contain the word “1/2”. Ordinarily I’d use something like =SUMIFS(B$1:B$6,A$1:A$6,"<>* 1/2 *"), but it’s not working. It works if I substitute “1/2” for “AAA”, for example, but that’s not what I want, I want to exclude all cells that have the word “1/2” in it. I realise the problem is that the ‘word’ is a number; is there a way around it?

Thanks!

[Regina]

When your text has the character ½ (= U+00BD) as in your post here, than you need to use that character in the criterion, not the three characters 1 / 2.

If you want to match a part of the cell content, then you need to deactivate the setting Search criteria = and <> must apply to whole cells in Tools > Options > Calc > Calculate. However, this setting is global for the document and not compatible with Excel.

[karolus]

hallo

=SUMIFS(B1:B6;A1:A6;"<>.*½$")

⇒ Tools ⇒ Options ⇒ Calc ⇒ calculate ⇒⇒ check [x] allow regular Expressions

[Maykay]

Thanks, karolus, that works. If you’d be so kind can you explain the expression you used ("<>.*½$") and also why changing from “Enable wildcards in formulae” to “Enable regular expressions in formulae” made this work?

Thanks a lot for the solution.

[karolus]

"<>"  # should not match…
".*"  # …any char with any lenght, followed by literal…
"½"   # … 
"$"   # exactly left of the »end of the Text«

without checked Regular Expressions, but entered as array-formula…ctrl+shift+enter :

=SUMIFS(B1:B6 ; RIGHT(A1:A6;1);"<>½")

[Maykay]

Ah. Interesting, thank you very much.

Say I wanted to exclude cells like “AAA 1/2 ABC”, how would you go about it?

[robleyd]

Remove the $ so the expression isn’t confined to end of text?

List of regular expressions

[Maykay]

I’ve tried, but Calc is making my life difficult

The expression I wanted to exclude was “XYZ (1/2 grf)”, but try as I might I couldn’t get it to work. After a bit I thought “Maybe the parentheses are screwing things up” so I changed the excluded expression to “XYZ - 1/2 grf” and suddenly the exclusions worked perfectly ("<>.*grf").

Don’t know why the parentheses would screw with the formula, but it’s OK.

[robleyd]

You don’t say exactly what you tried; so I’ll just remind you that () have a special meaning in regular expressions - see the link I mention above.

[Maykay]

Sorry, robleyd.

The cells I wanted to exclude from my SUMIFS were the ones that included the phrase “(1/2 grf)”. When I tried using “<>.*(1/2 grf)” it didn’t work, apparently because () have a meaning in expressions (I must’ve missed it in that link that you kindly provided).

Is there a way that I can target that phrase - (1/2 grf) - in my formula?

Thanks and sorry for the bother.

[robleyd]

You can escape the parentheses with a backslash - ( or ) - so they will be treated as a normal character. From the page I linked, which you may also have missed:

\
The special character that follows it is interpreted as a normal character and not as a regular expression meta character

There is also some information on the SUMIFS documentation page about regular expressions in SUMIFS.

[Maykay]

Thank you!

At first the backslash wasn’t working. But then I had a hunch and checked something - and voilà. “Search criteria = and <> must apply to whole cells” was enabled. I disabled it and now it works perfectly. I don’t know why it re-enabled by itself, I had already disabled it as per Regina’s advice.

Thank you all for your help, it was very valuable and now I have a better understanding of Calc because of it.

[Regina]

There exist these global settings:
(A) Enable wildcards in formulas. Excel compatible, if active.
(B) Enable regular expressions in formulas. Not Excel compatible, if active.
(C) Search criteria <> and = must apply to whole cell. Excel compatible, if active.

Depending on the above settings some further solutions are possible, here written for the three characters 1 / 2. The solutions would work the same for single character ½ instead, see my other comment.

(1)
=SUM(IF(ISERR(FIND("1/2";A1:A6));B1:B6;0))
Must be entered as array formula using Ctrl+Shift+Enter. Works independent from the settings (A), (B) and (C)

(2)
=SUMIFS(B1:B6;A1:A6;"<>1/2")
Option (C) must be deactivated.

(3)
=SUMIFS(B1:B6;A1:A6;"<>*1/2")
Option (A) must be activated.

Solutions with regular expressions are already given in other answers.