-1

I'm using the SUMIF function except that the column I'm trying to sum is a string like this: "AU $12.3" instead of just "12", how can I strip this string to just return the float value inside my SUMIF statement (in the E column)?

=SUMIF('may'!$D$1:$D$500,"*Amount*",'may'!$E$1:$E$500)
player0
  • 124,011
  • 12
  • 67
  • 124
Finger twist
  • 3,546
  • 9
  • 42
  • 52

1 Answers1

0

You could try something like this:

=SUMPRODUCT(ISNUMBER(SEARCH("Amount",May!$D$1:$D$500))*REPLACE(May!$E$1:$E$500,1,4,0))
lori_m
  • 5,487
  • 1
  • 18
  • 29
  • cool, this returns 0 for a strange reason, so I`ve tried the REPLACE by itself it works fine, any way to put in back in the SUMIF function instead ? – Finger twist Jun 13 '12 at 07:30
  • Not sure why you're getting 0, can you post a larger sample? SUMIF only allows ranges as arguments, you need to use SUMPRODUCT to work with arrays. – lori_m Jun 13 '12 at 07:58
  • I note google-spreadsheet tag. lori_m's formula will give you zero in google spreadsheet, try this version `=ArrayFormula(SUM(ISNUMBER(SEARCH("Amount",May!$D$1:$D$500))*REPLACE(May!$E$1:$E$500,1,4,0)))` – barry houdini Jun 13 '12 at 12:59
  • yes, this is a google spreadsheet one, thanks for pointing that out . The ArrayFormula is returning a parse error unfortunately, what would a parse error indicate ? – Finger twist Jun 14 '12 at 01:32