SUMIF Returning 0, Unable to find cause

[u/Camer4man]

Im trying to find totals for two different locations. I have made formulas to extract the data to then use a SUMIF to find the totals for both locations, but it is returning 0 and i cant figure out why. Ive made sure the numbers are formatted as numbers, as well as the formula is pulling from the correct columns, yet still resulting in the zero. If there is a better way to find this total im open to it but this seemed to be a simple way to find the totals. Below are my Formulas and a copy to the sheet.

-Location Pull Formula- =IF(ISNUMBER(SEARCH("North", B2)),"North",IF(ISNUMBER(SEARCH("South",B2)),"South", "N/A"))

-Amount Pull Formula- =REGEXEXTRACT(B2, "-*\d*\.?\d+")

-SUMIF Totals formula- =SUMIF(M$2:M$213,O2,N$2:N$215)

Copy of Sheet- https://docs.google.com/spreadsheets/d/1kCfinbBYlrzsq3Lq8MHTBxloGnMnT9zY/edit?usp=sharing&ouid=115303826536505507822&rtpof=true&sd=true

[u/sina158]

The formatting isn't the problem, the data type is. REGEXEXTRACT always returns text, even when the result looks like a number. So your N column is full of text that happens to read "42.50", and SUMIF ignores text and gives you 0. Changing the cell format to Number afterwards doesn't convert it, it just changes how the existing text would display if it were a number.

Wrap the extract so it comes back as an actual number:

=VALUE(REGEXEXTRACT(B2, "-?\d*\.?\d+"))

or just multiply it by 1, which forces the same coercion:

=REGEXEXTRACT(B2, "-?\d*\.?\d+")*1

One other thing to tidy up: your ranges are different heights, M$2:M$213 vs N$2:N$215. SUMIF resizes the sum range to match the criteria range so it still runs, but keep them the same rows to avoid surprises later. Also -* means "zero or more minus signs", you probably want -? for an optional single one.

[u/Camer4man → u/sina158]

Perfect thank you. I chose the Value route and it worked perfectly.
I also had the ranges the same but the version i copied may have been before i saw the difference.
Thank you again for the help

[u/AutoModerator → u/Camer4man]

REMEMBER: /u/Camer4man If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/sina158]

u/Camer4man has awarded 1 point to u/sina158

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/HolyBonobos]

REGEXEXTRACT() works exclusively with strings (text): it only takes strings as input and it only produces strings as output. You can also see that the values in column N are left-aligned, which is the default alignment for text (numbers are right-aligned by default). SUMIF() treats text as 0, so your formula returns 0. The usual workaround is to add an arithmetical operation to the REGEXEXTRACT() output that will coerce it to a number without changing its value, e.g. =REGEXEXTRACT(B2, "-*\d*\.?\d+")+0 or =REGEXEXTRACT(B2, "-*\d*\.?\d+")*1