as the title suggest, I'd like to add up some numbers but they contain letters that i'd like to ignore.
£4.0m
£5.0m
£6.0m
£5.0m
£2.5m
£2.0m
£4.0m
£4.0m
£8.5m
£7.0m
£2.0m
Those are the figures I would like it to add up. It would need to ignore the '£' and the 'm' as it won't let me change the format
Hi,
This array function will do as you ask
=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A1:A11,"£",""),"m","")))
enter using CTRL, SHIFT and ENTER.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
thanks for your help but just comes up with #value
Did you enter with CSE?
Array functions need to be entered in a specific manner
Enter the text into the function bar
Hold down CTRL and SHIFT, and then tap ENTER to confirm.
If done correctly, the formula will display inside curly brackets {=SUM(.........}
You can't put the brackets in manually
If you prefer to avoid the Array
=SUMPRODUCT(--SUBSTITUTE(A1:A11,"m",""))
confirmed with Enter
Given your UK locale the £ should not be an issue.
If you need to account for blanks
=SUMPRODUCT(--SUBSTITUTE(REPLACE(A1:A20,2,0,0),"m",""))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Auto Sum but ignoring FOC
......................................
Last edited by hunglytummy; 05-27-2014 at 04:22 AM.
@hunglytummy
it is not allowed to break in another one's question.
Please start your own one.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
He has and his problem has been solved.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks