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 02:12 PM.
Try:
=SUMPRODUCT(--(SUBSTITUTE(A1:A3,"*","")))
adjust range to suit.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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
Reply With Quote
Bookmarks