Tom’s Tutorials For Excel: Conditional SUM of Alphanumeric Data
Here’s another crazy example — but it happens — when you are faced with making mathematical sense of numbers within alphanumeric data.
This example shows how to sum numbers that follow a certain set of characters, in this case numbers following “GT” in column A. I complicated things a little more, by inserting a space in some cells between letters and numbers, as a data entry person might have done.
The array formula in cell C2 is
=SUM(IF(TRIM(LEFT(A5:A20,2))="GT",MID(A5:A20,3,255)+0))
This formula sums numbers, but you could also perform an AVERAGE or other function; the concept would be the same.
Recall, array formulas are applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly installed.
I am creating a spread sheet to track mine and my friends golf scores every time we play. I have created drop down boxes for the scores. 2 of the numbers that can be entered are 6D and 6S which we notate on our scores.I just want to be able to have the 6 in front of the D and S to count as a 6 when totaling the scores. 3+4+3+6D+6S=22
How can i change your formula to work for that simple math?
I am fairly new to excel in regards to writing complex stuff. Can you help me?
With your example numbers in A1:A6, sum with array formula
=SUM(IF(ISNUMBER(FIND(“D”,A1:A6)),SUBSTITUTE(A1:A6,”D”,””)+0))+SUM(IF(ISNUMBER(FIND(“S”,A1:A6)),SUBSTITUTE(A1:A6,”S”,””)+0))+SUM(A1:A6)
Be sure to commit that formula with Ctrl+Shift+Enter, not just with Enter.
Hey. thank you for the help. I tried the formula and it will sum without the letters, but when i add the letter the number shifts to the left of the cell and gets subtracted from the total.
The formula worked for me, getting a 22 result with the same data you exampled which I copied and pasted from your first message. Did you commit the formula by pressing Ctrl+Shift+Enter as I said, and are the range references accurate. If so, something different is going on.
I keep getting 10. I have been using the ctr+shift+enter. The only thing i changed was the A6 value to A5. The data only goes from A1:A5, if i leave the A6 value and put the formula in A6 I get an error.
I am not sure what I am doing wrong.
Please post the 5 values exactly as they are entered, say the 5-cell range they are in, and post the formula you entered. Something is amiss bit this seems fixable.
A1 3
A2 4
A3 3
A4 6D
A5 6S
A6=SUM(IF(ISNUMBER(FIND(“D”,A1:A5)),SUBSTITUTE(A1:A5,”D”,””)+0))+SUM(IF(ISNUMBER(FIND(“S”,A1:A5)),SUBSTITUTE(A1:A5,”S”,””)+0))+SUM(A1:A5)
ctrl+shift+enter
OK, mystery solved. This is not your fault, but take a very close look at the double-quote character which shows up 12 times in the formula. The way that character translates in html actually makes it a different ascii character than what I really typed here on my computer.
So what you need to do is go to cell A6 where your formula is and press the F2 key to get into edit mode. Then, where you see a double quote character (I counted 12 of them), manually delete them and replace them by typing in that double quote character in its place. I just did that and the formula works with Ctrl+Shift+Enter as you have properly done. I have come across this issue many times and it is annoying as hell but I cannot avoid it unless anyone reading this has an idea about how they get around it.
WOW. What a world of difference. I cant believe that slight difference in quotes ruined that code. Works perfect now.
On a side note do you know of any good sites that explain some of the formulas a little more in depth and how to implement them? I understand some of them but implementing them in the right situation drags me down a bit. Lots of trial and error.
Thank you very much for you help.
That kind of detailed instruction for all situations is not easily encapsulated in one book. This formula is a bit on the complex side and is so specialized for what you are doing, it’s more a case of understanding the individual components (which is how to learn formula construction) and when & how to incorporate them in a formula to do what you need in whatever individual circumstance you are faced with. There are some good formula books out there; look at reviews on Amazon and leaf through a few at the bookstore to see what feels right for you. It takes a while to pick up the nuances and how various functions play with each other. I’ve been working with Excel since 1993 and I continue to learn something new every day. You can also check out my Links page at https://www.atlaspm.com/excel/ for other sites.
I will definitely be back here for reference.
There is a lot to learn. Huge help with my problem at hand though. Thank You.
I post Excel tips every day on Twitter @TomUttis and on my FB business page and on LinkedIn.