-1

How can I create a formula for the following spread heet?

The text all have same value (AB=1) BC=1) (CD=1) etc. I want to place the text in a cell to place order, the text should give me total number and then if I need to remove text I want it to amended total.

i.e AB;BC;CD = 3 but then if I remove BC the total should be 2. 

CODE    PRODUCT TOTAL       1   1   1   1   1   1
                JB  SC  AR  KP  JN  KW
101                                 
102                                 
103     3   JB+AR+KW                        
104                                 
105     5   SC+JN+JB+KP+KW                      
106                                 
107                                 
108                                 
109                                 
110                                 
111                                 
112                                 
113                                 
114                                 
115                                 
116                                 
117                                 
118                                 
119                                 
120                                 
121                                 
122                                 
123                                 
124                                 
125                                 
126                                 
127                                 
128
Improve this question
7
  • 1
    I get that you are basically counting 2-character pairs (I think). Your example is a bit confusing since what appears to be column headings doesn't really relate to the explanation. I assume the 100-series numbers on the left are the codes, or unique IDs of some kind. You've got six 2-character pairs at the top. Is that just a list of possible values? Two of the rows have 3 and 5 pairs, respectively. Are the values always delimited by "+", or does "+" represent the math you want to do with the count?
    – fixer1234
    Feb 5, 2016 at 23:19
  • 2
    The question is very confusing at this point.
    – Tyson
    Feb 5, 2016 at 23:20
  • Thank you for your suggestion Sathya & Tyson. the column on the left are product code
    – Harry
    Feb 7, 2016 at 12:15
  • Thanks @Sathya and I will try to clarify my problem. The Column A represents product codes we order for residents. Letters column E,F, G etc represent initials of individuals, each initial's value =1 (for 1 item they order) that value will always remain 1 as they order 1 item at a time. now if 25 people order product from column A7, we need to enter initials in column D7 all those initials should give me a total to order in column C7 these orders change regularly but we also need to keep record of who individuals orders.
    – Harry
    Feb 7, 2016 at 12:31
  • @Harry: Just stopped back to see if you had solved your problem. From your explanation, Maher's answer seems to provide two solutions. His action is in col B. You replied that you couldn't get it to work because you got 0 in col C. If his formulas worked, would that be what you trying to accomplish? If not, use that as a starting point to explain how what you want is different. If it is what you're looking for, try reproducing his example exactly to verify that it works. Then we can deal with moving things to match your layout.
    – fixer1234
    Feb 9, 2016 at 5:33

1 Answer 1

Reset to default
2

You can get the desired result without assigning values. you can count letter, exclude the "+" plus signs and divide by two.

  1. for this example paste the following formula in B5

=LEN(SUBSTITUTE(C5,"+",""))/2

enter image description here

  1. Copy and paste the cell and it'll automatically adjust C5 for the row number

OR

you can use this formula instead =LEN(C5)-LEN(SUBSTITUTE(C5,"+",""))+1 this will count the "+" plus signs and add one. this formula will work with any data length not just ones with two charterers.

Thanks Fixer1234 for the suggestion.

Improve this answer
3
  • 1
    Or, count the "+"s and add 1.
    – fixer1234
    Feb 6, 2016 at 3:47
  • Actually that'd be even better, because It'll works with different code length like (SCC+JN+JBJB+KP+K). the formula for that would be =LEN(C5)-LEN(SUBSTITUTE(C5,"+",""))+1
    – Maher Fattouh
    Feb 6, 2016 at 11:25
  • Thanks for suggestion, I couldn't get it to work, result is 0 in column C, Column A are product codes we order for residents. Letters column E,F, G etc are initials of individuals, each initial's value =1 (for 1 item they order) that value always remain 1 as order 1 item a time. if 25 people order product column A7, we enter initials in column D7 those initials should give me a total in column C7 these orders change regularly but we need to keep record of who individuals orders what. Hopefully my explanation is clearer. sorry
    – Harry
    Feb 7, 2016 at 12:40

Not the answer you're looking for? Browse other questions tagged .