excel - Sum of text Does not contain in Range - Stack Overflow

admin管理员组

文章数量:1122826

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 :-)

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 :-)

• excel
• excel-formula

Share Improve this question Follow edited Nov 21, 2024 at 13:00 Mayukh Bhattacharya 27k88 gold badges2929 silver badges4242 bronze badges asked Nov 21, 2024 at 11:54 SrikanthSrikanth 5911 silver badge44 bronze badges 2

• 2 Because it tests the list of items against the criteria in F2 or F5 one letter at a time. There is only one row that is =A, but 10 rows that are <>A. – Tom Sharpe Commented Nov 21, 2024 at 12:06
• … Why not just add everything, and then subtract the "include" version? – Chronocidal Commented Nov 21, 2024 at 13:08

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

The correct answer should be 13, not 10, unless I am missing something.

You can break down your formula to understand why you are getting 128.

When you multiply a horizontal vector by a vertical vector, the result is a matrix.

=TEXTSPLIT(F5,",")<>B2:B12

Result:

When you multiply that matrix by column C, you obtain:

and the sum of all those numbers is 128.

One way to do this with TEXTSPLIT is to process each row separately.

=SUM(BYROW(TEXTSPLIT(F5,",")<>B2:B12,LAMBDA(ARR,AND(ARR)))*C2:C12)

which would give the answer of 13

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))

---

A very simple formula

[E2]=SUM($C$2:$C$12*ISERROR(FIND($B$2:$B$12,D2)))

fill down.

This formula easily inversible. While it counts by "Not equal to", the formula with the inverted condition counts by "Equal to":

[E2]=SUM($C$2:$C$12*NOT(ISERROR(FIND($B$2:$B$12,D2))))
or 
[E2]=SUM($C$2:$C$12*ISNUMBER(FIND($B$2:$B$12,D2)))

So, different condition expressions behave differently. Generally, always keep in mind the basic low:

NOT(AND(X,Y))=OR(NOT(X),NOT(Y))

The main logical error in your formula is that: while the task is to sum items in the list / not in the list, TEXTSPLIT(F2,",")=B2:B12 performs the revers operation to check if list's element is among items. So, TEXTSPLIT(F2,",")<>B2:B12 for each F2 list's element returns ALL items which are not equal that element.

本文标签： excelSum of text Does not contain in RangeStack Overflow