5

I would like to sum a range where a corresponding range does not contain "2nd".

Here is the opposite of what I want: 

=sum(filter(fUsers,regexmatch(fLabel,"2nd")))

Which runs and returns 1,150.

I have looked at and tried to copy these SO posts:

I know that if I go to the data set in question and use filtering, I should have 15,017 returned.

Based on these posts here is what I have tried:

=sum(filter(fUsers,regexmatch(fLabel,"^(?!2nd$).*")))  // returns #N/A no matches found in filter evaluation.

=sum(filter(fUsers,regexmatch(fLabel,"^(?!.*2nd).*$")))  // returns #N/A no matches found in filter evaluation.

How can I get Gsheets to sum values in range fUsers where fLabel does not contain "2nd"?

Community
  • 1
  • 1
Doug Fir
  • 19,971
  • 47
  • 169
  • 299

1 Answers1

5

You might want to use a built-in NOT() operator (see Filter Sample Usage):

=sum(filter(fUsers,not(regexmatch(fLabel,"2nd"))))

EDIT:

Accoding to RE2 regex syntax, negative look-aheads are not supported.

(?!re) before text not matching «re» NOT SUPPORTED

This means, you cannot check with a regex if a sequence of characters is missing in the input string. You could check if 1 symbol is missing, but not several.

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • It is not possible to achieve with a regex. You could check if a cell has no hyphens with `^[^-]*$`, but when you have several subsequent characters, you need a look-around. Sorry, just `NOT()` is the only Gsheet way of doing this. – Wiktor Stribiżew May 25 '15 at 14:34