# Sum of numeric values within cells also containing non-numeric characters

1. ## Sum of numeric values within cells also containing non-numeric characters

Hello,
I need to calculate the sum of cells in a column as below, eg

12
20
18*
4*
0
65*
10

Where the answer would be 129

In other words the total ignores the asterisks (and yes these are asterisks not wild cards)

Any assistance greatly appreciated, thanks.

2. ## Re: Sum of numeric values within cells also containing non-numeric characters

try:

``Please Login or Register  to view this content.``
if you have blank cells in the range:

``Please Login or Register  to view this content.``

3. ## Re: Sum of numeric values within cells also containing non-numeric characters

thanks icestationzbra, unfortunately that returns the dreaded #value! when I try it?

4. ## Re: Sum of numeric values within cells also containing non-numeric characters

It works for me.

Make sure there are no blanks or other NON numeric values (like Hello*) in the range.

5. ## Re: Sum of numeric values within cells also containing non-numeric characters

tyvm to both of you, it works on one computer and not the other so i'll cut and paste the two sheets and establish what the glitch is but my main dilemma is solved, thank you

6. ## Re: Sum of numeric values within cells also containing non-numeric characters

Anyway, try to replace blank cells with 0
=SUMPRODUCT(SUBSTITUTE(IF(A2:A8="",0,A2:A8),"*","")*1)
Conffirmed with Ctrl-Shift-Enter

7. ## Re: Sum of numeric values within cells also containing non-numeric characters

ok i see the difference now, with blank cells in the range it doesnt work. I need to have blanks in the range and these cannot be zero but must be blank as they will populate over time. Any suggestions for this extended issue gatefully received

8. ## Re: Sum of numeric values within cells also containing non-numeric characters

To avoid blank cells, have you tried my solution in #6?

9. ## Re: Sum of numeric values within cells also containing non-numeric characters

Try this array formula entered with CTRL + SHIFT + ENTER

=SUM(IF(A1:A10<>"",SUBSTITUTE(A1:A10,"*","")+0))

10. ## Re: Sum of numeric values within cells also containing non-numeric characters

@Jonmo1 ...... now that my friend, is pure genius, many thanks.

11. ## Re: Sum of numeric values within cells also containing non-numeric characters

Or, confirmed with just Enter,

=SUMPRODUCT(--(0&SUBSTITUTE(A1:A10,"*","")))

12. ## Re: Sum of numeric values within cells also containing non-numeric characters

That's great!
Originally Posted by shg
=SUMPRODUCT(--(0&SUBSTITUTE(A1:A10,"*","")))

13. ## Re: Sum of numeric values within cells also containing non-numeric characters

Almost great -- it doesn't work with negative numbers ...

14. ## Re: Sum of numeric values within cells also containing non-numeric characters

shg: almost almost great, except cells with text
jonmo: work great all the cases.

15. ## Re: Sum of numeric values within cells also containing non-numeric characters

Originally Posted by bebo021999
jonmo: work great all the cases.
Mine doesn't work on cells with text either..
Need to nest some if's to test for text entries..

16. ## Re: Sum of numeric values within cells also containing non-numeric characters

{=sumproduct(iferror(--substitute(a1:a10,"*",""),0))}

17. ## Re: Sum of numeric values within cells also containing non-numeric characters

hello to all on this thread.... just anote to say thanks to all for the way in which you extended the debate on this problem and then solved it......since my original post I have extended my database and have scenarios where the range contains, not only blanks but also text., so your intellect has not been wasted, thanks again

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1