Hi, I need help.
I've created an excel sheet with a drop down list which includes numbers (and letters) in brackets. Eg: 8 (AL), 16 (AL) etc. See attached picture
I need to create a formula that sums only the numbers in a column of cells excluding any letters or a cell with a only a letter. I dont want to split the cell as I need the drop down lists to include the letters as to specifiy something. I'll be using conditional formatting with the letters such as AL \ LSL
Can anyone help?
Grant
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. As you are new here I'll do it for you this time.)
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Hi, here is a copy of the sheet I am trying to work with
OK - give us a clue!!! Where are the data you want to sum and where are the mocked up results you are expecting?
OK - I think I found it!
Try this:
SUM(IFERROR(VALUE(SUBSTITUTE(A1:C3,"C","")),""))
or this:
=SUM(IFERROR(VALUE(LEFT(SUBSTITUTE(E8:E99,"C",""),2)),""))/76
... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Last edited by AliGW; 08-04-2019 at 09:57 AM.
In AM8 then copy down
=IF(ISNUMBER(FIND(" ",AJ8)),LEFT(AJ8,FIND(" ",AJ8)-1),"")
Use AM8:AM80 for validation.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
If all of the numbers are only 1 or 2 digits followed by a space, thenshould do it.Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Maybe this
Enter formula in AM8 and copy down
=IFERROR(-LOOKUP(1,-LEFT(AJ8,{1,2,3,4,5})),"C")
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks