1

I have a range, containing numeric values & blank cells. Some of the numeric values will have * as suffix.

10* 5 7  9  25* 10

When I do a SUM(A1:A8), I get result of 5+7+9+10 = 31, the required output. Now, I also require the total sum irrespective of * suffix. I'm trying to solve by using

SUMPRODUCT(SUBSTITUTE(A1:A8,"*",""))

It works out to

SUMPRODUCT({"10","5","7","","9","","25","10"})

And outputs 0 since all are text values. When I use 

SUMPRODUCT(value(SUBSTITUTE(A1:A8,"*","")))

It works out to 

SUMPRODUCT({10,5,7,#VALUE!,9,#VALUE!,25,10})

and finally outputs #Value!. Can someone help me to solve this? Thanks for your time.

Ashok
  • 1,074
  • 3
  • 14
  • 24
  • Not a programming question. Should be asked at SuperUser – brettdj Jun 21 '12 at 10:11
  • @brettdj, Actually I've searched StackOverflow for any answers before posting.. I found so many excel formula related questions posted here, I thought its normal... – Ashok Jun 21 '12 at 17:29
  • This issue has been addressed here: http://meta.stackoverflow.com/questions/261875/are-excel-formulas-on-topic – intcreator Jul 28 '15 at 21:59

2 Answers2

5

I assume that the blanks are the problem because when you use =VALUE("") you get an error. Try concatenating a zero to the start of SUBSTITUTE so that blanks become zeroes

=SUMPRODUCT(VALUE(0&SUBSTITUTE(A1:A8,"*","")))

or you can use +0 in place of VALUE, i.e.

=SUMPRODUCT((0&SUBSTITUTE(A1:A8,"*",""))+0)

barry houdini
  • 45,615
  • 8
  • 63
  • 81
0

Try

=SUM(VALUE(SUBSTITUTE(A1:A8,"*","")))

and enter it with Ctrl + Shift + Enter, instead of just Enter. This makes it an array formula, and it will treat the A1:A8 range as an array for the SUBSTITUTE() function. Thus, SUBSTITUTE() now evaluates each individual value in A1:A8 separately. VALUE() converts the text to numbers, and sum() adds all of them up.

Edit: The formula =SUMPRODUCT(VALUE(SUBSTITUTE(A1:A8,"*",""))) seems to be working for me. (Normal formula, not an array formula).

playercharlie
  • 629
  • 1
  • 6
  • 17