Function Sumproduct - Search - Left

[flint001]

Good morning everyone.

I've been trying to find a solution to a problem through the sumproduct function for several days, I've tried several approaches and I can't solve it.

In each line I have to add the amount of "A", "B" or "C" and in each cell of that line I have for example: "3A + 1B" and in another cell "33B + 1C". I tried using sumproduct, search to find the letter and then LEFT to remove the 2 characters only. Is there any way to solve this without using VBA?

Thank you in advance for your help.

[Glenn Kennedy]

What Excel version do you have?? (Windows 10 is not an Excel version!!).

[flint001]

Excel 2010. I will review where I put thatinformation.

[Glenn Kennedy]

Thanks... It's in your User Control panel...

[JohnTopley]

Use EVALUATE function

See https://www.myonlinetraininghub.com/...luate-function

in A4 and copy across

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"A","*"&$W$2),"B","*"&$X$2),"C","*"&$Y$2)

in A5 and copy across

=IFERROR(Result,"")

RESULT is named range referes to: =EVALUATE(A$4)

NOTE: file is XLSM

[Glenn Kennedy]

Mmm. That's not they way I interpreted this. If this is INCORRECT, remove doubts by TELLING us your expected answers...

[flint001]

Thanks for the answer.
I think I didn't explain myself well, the idea would be to add the amount of "A", "B" and "C", and for that reason I was using the sumproduct function, together with search and left.

If you have 1A + 1B; 1C + 1A; 33B + 1C; ... at the end you have 2 "A", 34 "B" and 2 "C"
if on the bottom line you have 31A + 1B; 1C + 1A; 33B + 1C; ... at the end you have 32 "A", 34 "B" and 2 "C"

I can't get the sum in the sumproduct function to add the values placed next to the letters.

Example attachment.

[Glenn Kennedy]

OK. This is simple... one short formula... with O365. In anything >= Excel 2013, it can also be done but with a little effort.... However, in Excel 2010, I am stuck!! I can do the A's easily enough, but cannot get the Bs and Cs...

[JohnTopley]

Please Login or Register  to view this content.

in W2

=count_ABC($A2:$V2,W$1)

copy across and down

[Glenn Kennedy]

Woo Hoo! I never picked up VBA... Too late, now...

[JohnTopley]

It's never too late to learn "new tricks" !!!!!

I consider myself no more than competent in VBA : I (sometimes) use it because the formulae you (and many others use) hurt my brain!!

But this is an example where VBA can score vs a (potentially) complex formula.

[flint001]

Good afternoon, thanks for the help, because I was not seeing any formula to solve this.

I will try to keep this solution in vBA, because sometimes we have problems with VBA code.

Thanks again for the help.

[JohnTopley]

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.