# Extract a number with a letter

I am trying to extract a number and add them in a range of cells if they are attached to a certain letter.

Ex: Cell B1=5C, Cell C1=3C, Cell D1=2B Looking to extract and add all cells with the letter C. In this case my total should be 8. Also, if the Cell only contains a C it should equal 8. If the last part isn't easily easily done, I can always make it 8C.

• Don't forget to mark as the answer if it solved the problem for you. – CodeCamper Nov 18 '19 at 15:08

## 1 Answer

Does exactly what you asked, C alone is 8 and adds up only the C numbers. Per your example it only checks B1:D1 for C.

``````=arrayformula(sum(iferror(if(SEARCH("C",B1:D1),if(len(B1:D1)=1,8,VALUE(substitute(upper(B1:D1),"C",""))),0),0)))
``````
• This work perfectly! Thank you! – Joel Munoz Nov 25 '19 at 5:20
• Is there a way to add other letters to look for? For example, if I want to add anything with a letter C,P and F? Example: Cell B1=3C, Cell C1=6P, Cell D1=5F, Cell E1=4S; the whole thing should total 14. – Joel Munoz Nov 25 '19 at 5:43
• @JoelMunoz what do these letters mean, maybe you should ask a new question about this, because your problem can be simplified or perhaps even more complicated than you think. – CodeCamper Nov 25 '19 at 14:43
• I'm trying to make a sick call sheet. For example C = Comp time, P = Personal Day, F = Family Reason. All these should be deducted out of sick leave with is (S) but I need to track them individually because each staff member is only allowed so many of each day. – Joel Munoz Nov 29 '19 at 23:46
• @JoelMunoz What do the numbers mean? maybe it makes sense to do this in separate columns if it gets too complicated. – CodeCamper Dec 1 '19 at 18:30