Microsoft Excel - Sum values with symbols attached eg 6* + 5 = 11

Asked By kev ryan
31-Oct-09 10:52 PM
Hi Everyone

Was wondering if there is a way to SUM values with "*" attached to them eg 6 + 5* + 4 + 7* = 22.

Excel ingores cell values with * attached when using SUM function. eg 6 + 5* + 4 + 7 = 10.

I don't need the * retained in the answer, just the SUM of all values, with or without *.

Thankyou in advance.

Kev
  Jonathan VH replied to kev ryan
01-Nov-09 06:42 AM

You can use the SUBSTITUTE function to replace the *s with blanks. Excel will then treat the result as a number if you use the addition (+) operator as in your examples. This will not, however, work with a function like SUM, because SUM treats any string as zero. I.e. 6+SUBSTITUTE("5*","*","")+4+SUBSTITUTE("7*","*","") does equal 22, but SUM(6,SUBSTITUTE("5*","*",""),4,SUBSTITUTE("7*","*","")) equals 10.

If you wish to use the SUM function on a range of values including the strings with trailing * chanacters, you can use an array fuction:

=SUM((SUBSTITUTE(A1:A4,"*","")*1)

After typing or copying the formula, press Ctrl-Shift-Enter together rather than just Enter. This will cause the formula to display in the formula bar surrounded by curly braces, signifing that it is an array function, i.e.:

{=SUM((SUBSTITUTE(A1:A4,"*","")*1)}

This works by using SUBSTITUTE on each value and then multiplying that value by 1, which causes the product to be a number before it is SUMmed.

thanks  thanks

01-Nov-09 07:47 PM
Thanks very much Jonathan, most helpful.

Kev

{=SUM((SUBSTITUTE(A1:A4,  {=SUM((SUBSTITUTE(A1:A4,"*","")*1)} won't work with blank cells in range

02-Nov-09 12:29 AM
Jonathan

I should have mentioned earlier that not all the cells in the range to be summed have data in them yet.

The function {=SUM((SUBSTITUTE(A1:A4,"*","")*1)} works if there is data in only cells A1:A4, but extending the range to include blank cells causes it to return the VALUE error.

I was wondering if there was a way to get this function to include blank cells in the sum value?

Thanks

Kev
{=SUM((SUBSTITUTE(A1:A4,  {=SUM((SUBSTITUTE(A1:A4,"*","")*1)} won't work with blank cells in range
02-Nov-09 12:34 AM
Jonathan

I should have mentioned earlier that not all the cells in the range to be summed have data in them yet.

The function {=SUM((SUBSTITUTE(A1:A4,"*","")*1)} works if there is data in only cells A1:A4, but extending the range to include blank cells causes it to return the VALUE error.

I was wondering if there was a way to get this function to include blank cells in the sum value?

Thanks

Kev
  Jonathan VH replied to kev ryan
02-Nov-09 06:48 AM

{=SUM(IF(ISBLANK(A1:A4),0,SUBSTITUTE(A1:A4,"*","")*1))}

I'm surprised you couldn't figure out something similar.

  kev ryan replied to Jonathan VH
02-Nov-09 07:21 PM
I was trying to use something very similar, but for the life of me couldn't get it to work.  Must have had a mental blank at the time.

Thanks heaps.
  asdf replied to kev ryan
30-Jun-10 02:46 PM

This should work :

=SUM((SUBSTITUTE(C14+C18+C22+C25+C31+C34,"*","")*1))

Create New Account
help
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE. . . . max. limit 8 :( Excel I'm having a problem while using excel 2002. I have an estimated amount of 3500 cells containing text. I also have 450 this list of 450 words so that those words will be transformed into each abbreviation? Excel Miscellaneous Discussions Microsoft Excel (1) Excel (1) SUBSTITUTE (1) Worksheet (1) LOOKUP (1) VBA (1) Harlan (1) B58329abe022da81 (1
Is there a way to short this substitute formula? Excel I want to remove all periods, hyphens and comma's. The below works but is there a better solution? = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K35, ".", ""), " ", ""), "-", "") - - Wag more, bark less Excel Miscellaneous Discussions Microsoft Excel (1) SUBSTITUTE (1) Hyphens (1) From a formula, not really. The old_text argument cannot be
Case sensitivity of functions in Excel Excel Hi, Which Excel functions are case sensitive and which are not? Can anyone shed some light or point to some resource that explains the case sensitivity of functions in Excel? Thanks in advance. Regards, Raj Excel Worksheet Discussions Biff Microsoft Excel MVP (1) Microsoft Excel (1) EXACT function (1) Excel (1) SUBSTITUTE (1) EXACT (1) FIND (1) Functions in
limits to multiple subsitute funtion?? Excel Is there a limit to how many substitute functions I can have in one formula? It does not seem to let me have more than 8. If there is a limit, is there a work around? = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BM2, "Oscar Heyman", "16431685"), "Buccelati", "16431678"), "Cartier", "16431674"), "Chanel", "16431677"), "Chopard", "16431686"), "David
Creating notches in box whisker plots in Microsoft Excel Excel Does anyone know how to create the notches in the vertical box whisker charts? An be found in figure 10 of the following article: http: / / www.qualitydigest.com / oct97 / html / excel.html Thank you!! Excel Charting Discussions PERCENTILE (1) MEDIAN (1) Excel (1) QUARTILE (1) MAX (1) MIN (1) Sort (1) Hi, The technique describes how to A$50, 0.25)) / (1.35*50)))) You ROCK!!! On Sat, 16 Jun 2007, in microsoft.public.excel.charting, Andy Pope <andy@andypope.info> said: Any particular reason for preferring PERCENTILE(<range> , 0