2

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 – user0 Feb 22 at 9:52
0

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". – FOZDEMIR Feb 22 at 11:44
  • so it works for you or still need further assistance? – user0 Feb 22 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. – FOZDEMIR Feb 22 at 13:13
  • @FOZDEMIR answer updated. try 2nd one – user0 Feb 22 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/… – FOZDEMIR Feb 22 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.