2

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.

Improve this question
1
  • Don't forget to mark as the answer if it solved the problem for you.
    – CodeCamper
    Nov 18, 2019 at 15:08

1 Answer 1

Reset to default
2

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)))
Improve this answer
5
  • This work perfectly! Thank you!
    – Joel Munoz
    Nov 25, 2019 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, 2019 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, 2019 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, 2019 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, 2019 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.