Adding cells containing asterisks
Hi,
I know its probably an easy question but I am a beginner.
I have a number of cells I want to add up but some of them contains asterisks so excel just ignore those.
for ex. 2, 10*, 10**, and i want to say 22.
How can I force excel to ignore those and just add the numeric values?
thanks in advance!!
Last edited by andy86; 12-08-2011 at 03:12 PM.
Try:
=SUMPRODUCT(--(SUBSTITUTE(A1:A3,"*","")))
adjust range to suit.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
i tried that but it still returns #value error
Is there any other text in those cells?
Are they in separate cells or are there multiple numbers in same cells?
no there no other text except asterisks and all in separate cells.
i tried the formula in different sheet with simple number same error (((
You'd get an error if there are blanks in the range - to accommodate blanks try this version
=SUMPRODUCT(--(0&SUBSTITUTE(A1:A10,"*","")))
Audere est facere
yeah didint think about that!
thanks it worked perfect!!!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks