Google Sheets – Sum Values in Cells with Numbers and Text

filterformulasgoogle sheetsgoogle-sheets-arrayformularegex

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?

Best Answer

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]+")))

Related Topic