SUM total using FIND from one CELL

[exc4libur]

Hi guys,

Nice facelift to the forum! It looks awesome, cleaner and more user-friendly than a lot of others I'm part of! Congrats.

Let's cut to the chase shall we I have a little problem, I am trying to find the total sum using find and other formulas, but I didn't have any luck so far.
Inside the file, its self-explanatory, simple and clean. If you need more details, I'll be more than glad to assist!

Thanks in advance,
EXC4LIBUR.

 

[Faseeh]

This could be super simple if you layout data properly. Please see this sheet:

Regards,

 

[Haseeb A]

Hello,

Try this Array Formula, with CTRL+SHIFT+ENTER

=SUM(A$7:A$9*MMULT((TRIM(MID(SUBSTITUTE(B$7:B$9,";",REPT(" ",999)),TRANSPOSE(ROW(A$1:A$20))*999-998,999))=A2)*(SUBSTITUTE(0&TRIM(MID(SUBSTITUTE(C$7:C$9,";",REPT(" ",99)),TRANSPOSE(ROW(A$1:A$20))*99-98,99)),"%","")%),ROW(A$1:A$20)^0))

Assuming MAX CO in a cell will be up to 20.

 

[exc4libur]

Hi Haseeb. Thank you very much, that was exactly what i needed!
Cheers.

Hello,

Try this Array Formula, with CTRL+SHIFT+ENTER

=SUM(A$7:A$9*MMULT((TRIM(MID(SUBSTITUTE(B$7:B$9,";",REPT(" ",999)),TRANSPOSE(ROW(A$1:A$20))*999-998,999))=A2)*(SUBSTITUTE(0&TRIM(MID(SUBSTITUTE(C$7:C$9,";",REPT(" ",99)),TRANSPOSE(ROW(A$1:A$20))*99-98,99)),"%","")%),ROW(A$1:A$20)^0))

Assuming MAX CO in a cell will be up to 20.

 

[exc4libur]

Faseeh, thank you for the help. However, the data was set that way, because I couldn't add more columns.
Cheers!

This could be super simple if you layout data properly. Please see this sheet:

Regards,