# Sum value in cells that contain both number and text

I need to sum a range of cells with text in them.

Example:

``````a1= "25,00 market"  b1="24,00 credit card" c1="75,11 food"
a2= "10,00 sweets"  b2="9,00 market"       c2="24,00 keyboard"
``````

I want to sum all the numbers in cells that contain "market" in this range. Something like:

`````` =SUMIF(a1:c2; "*market*"; a1:c2 )
``````

But this always returns the value `0`.

What am I doing wrong?

• the issue is that only you see the numbers - sheet doesn't. sheet sees only text so you need to extract those (visual) numbers to become actual ones and then work on it Feb 22, 2019 at 9:52

### US syntax:

```=SUMPRODUCT(ARRAYFORMULA(REGEXREPLACE(REGEXEXTRACT(FILTER({A1:A;B1:B;C1:C},
ISNUMBER(SEARCH("*market*", {A1:A;B1:B;C1:C}))), "[0-9]*\,[0-9]+[0-9]+"), ",", ".")))``` ### EU syntax:

```=SUMPRODUCT(ARRAYFORMULA(REGEXEXTRACT(FILTER({A1:A;B1:B;C1:C};
ISNUMBER(SEARCH("*market*"; {A1:A;B1:B;C1:C}))); "[0-9]*\,[0-9]+[0-9]+")))``` • This caused a formula parse error for me. I modified it a bit to '=SUMPRODUCT(ARRAYFORMULA(REGEXREPLACE(REGEXEXTRACT(FILTER({A1:A;B1:B;C1:C}; ISNUMBER(SEARCH("market"; {A1:A;B1:B;C1:C}))); "[0-9]*\,[0-9]+[0-9]+"); ";"; ".")))' but it removed commas and showed the value "3400". Feb 22, 2019 at 11:44
• so it works for you or still need further assistance? Feb 22, 2019 at 12:44
• Thank you for your answers but I still do need help unfortunately. What I need is 34,00 here, not 3400. When I format the cell as number or currency, it converts to 3400,00 but that's not what I need either. Feb 22, 2019 at 13:13
• @FOZDEMIR answer updated. try 2nd one Feb 22, 2019 at 13:51
• Second one kinda worked. Something interesting is happening here. When I change spreadsheet Locale settings to "UK" it works but writes "34.00". When I switch it back to "Turkey" it writes "34,00" when I make a change in one of the columns that contains text "market" it changes back to "3400" I'm confused now. I'm sharing the sheet link: docs.google.com/spreadsheets/d/… Feb 22, 2019 at 14:02