Sum of text Does not contain in Range

Hi I have a data of more than 15K rows.

I want to take sum of items which are in the list and also sum of items which are does not in the list.

I am getting result with the textsplit function when it is includes with the range. below is my formula:

=SUM(--(TEXTSPLIT(F2,",")=B2:B12)*C2:C12)

If I am using the same formula for "Not Equals to" it is not giving the exact result. I stuck here what could be the reason.

=SUM(--(TEXTSPLIT(F2,",")<>B2:B12)*C2:C12)

but some how I managed my work with Filter function.

=SUM(FILTER(C2:C12,(B2:B12<>"A")*(B2:B12<>"D")*(B2:B12<>"E")*(B2:B12<>"H")*(B2:B12<>"K")))

Anyone could help why the Textsplit function is not giving exact result when I am using "Not equals to"

Thank you :-)

Another alternative way and yes it should be 10 and not 13 when it is not equal to the split value of A,D,E,H,K

---

=LET(
 a, TEXTSPLIT(E2,","),
 SUM(FILTER(C$2:C$12,MMULT(N(a=B$2:B$12),SEQUENCE(COLUMNS(a))^0)=0)))

---

To get the reverse that equals to then :

=LET(
 a, TEXTSPLIT(E2,","),
 SUM(FILTER(C$2:C$12,MMULT(N(a=B$2:B$12),SEQUENCE(COLUMNS(a),,,0))>0)))

---

Per OP:

Thank you @Mayukh, Just consider A=1,B=1,C=1....K=1. A,D,E,H,K includes result 5 and B,C,F,G,I,J does not includes result 6. I hope this will give clear result. – Srikanth

---

The given formula is correctly working without any issues, please try to test on your end, the following screenshot demonstrates that :

---

We can exclude the FILTER() function:

=LET(
     a,TEXTSPLIT(E2,","),
     SUM((MMULT(N(a=B$2:B$12),SEQUENCE(COLUMNS(a),,,0))>0)*C$2:C$12))

---

And

=LET(
     a,TEXTSPLIT(E2,","),
     SUM((MMULT(N(a=B$2:B$12),SEQUENCE(COLUMNS(a),,,0))=0)*C$2:C$12))

---