CODEWITHSUNDEEP
excelexcel-formulasumsumifs
Error 1004
Error 1004

Reputation: 8220

SUMIF with do not include part - Excel

I m using SUMIF formula to get the sum of rows which include a specific text, Test. However, I want to sum up only rows which do not include the color Red.

Currently I m using the below formula:

=SUMIF(A1:A6;"*Test*";B1:B6)-SUMIFS(B1:B6;A1:A6;"*Test*";A1:A6;"*Red*")

enter image description here

Is there a better/clearer formula?

Test 1
Test 1 Red 2
Test 2 3
Test 1 Red 4
Test 1 Blue 5
Test 3 6

Upvotes: 0

Views: 107

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27243

Try using the following formula, exclude the part you don't need in the criteria itself using wildcard:

enter image description here

=SUMIF(A1:A6,"<>*Red*",B1:B6)

Or,

=SUM((ISNA(TEXTBEFORE(A1:A6," Red")))*B1:B6)

Or, to include the word Test and not Red, then SUMIFS()

=SUMIFS(B1:B6,A1:A6,"<>*Red*",A1:A6,"*Test*")

With multiple criteria to Exclude:

enter image description here

=SUM((ISNA(TEXTBEFORE(A1:A6,{" Red"," Blue"})))*B1:B6)

Or using SUMIFS()+SUM()

=SUM(B1:B6)-SUM(SUMIFS(B1:B6,A1:A6,{"*Red","*Blue"}))

=SUMIF(A1:A6,"Test*",B1:B6)-SUM(SUMIFS(B1:B6,A1:A6,{"*Red","*Blue"},A1:A6,"Test*"))

Instead of hard-coding, its better to use cell references so it becomes more dynamic and one can toggle around whenever a color is required or not:

enter image description here

=SUMIF(A1:A11,"Test*",B1:B11)-SUM(SUMIF(A1:A11,"*"&List[List],B1:B11))

Upvotes: 1

nkalvi
nkalvi

Reputation: 2614

The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria

This may be the easiest (sum range ANDing criteria).

=SUMIFS(B2:B7, A2:A7, "*Test*", A2:A7, "<>*Red*", A2:A7, "<>*Blue*")

Do you want to 'OR' matches (for example, "Apples" OR "Oranges") later?

Upvotes: 0

Related Questions