When this formula is entered, the result is $136. This is the sum of numbers in the range F5:F16 where corresponding cells in C5:C15 are not equal to “Red”. Note that the SUMIFS function is not case-sensitive.
SUMIFS solution
In the example shown, the solution is based on the SUMIFS function. The formula in cell I5 is: In this formula, sum_range is F5:F16, criteria_range1 is C5:C16, and criteria1 is “<>red”. The result ($136) is the sum of numbers in the range F5:F16 when corresponding cells in C5:C15 are not “Red”. Note that the SUMIFS function is not case-sensitive. With a criteria of “<>red”, SUMIFS will exclude “RED”, “Red”, and “red”. Also note that in the SUMIFS function, sum_range always comes first. To sum numbers when the state is not “TX” , you can adapt the formula like this: In this formula, sum_range is F5:F16 as before, criteria_range1 is D5:D16, and criteria1 is “<>tx”. For more information about using SUMIFS to apply multiple criteria with logical operators (>,<,<>,=) and wildcards (*,?), see this page.
SUMIF solution
The SUMIF function is an older function in Excel that supports only a single condition. To solve this problem with the SUMIF function, you can use a formula like this: In this formula, range is D5:D16, criteria is “<>tx”, and sum_range is F5:F16. Note that in the SUMIF function, sum_range always comes last. The result ($136) is the same as with the SUMIFS function above. For more information about using SUMIF to apply criteria with logical operators (>,<,<>,=) and wildcards (*,?), see this page.
Notes
For a case-sensitive solution, see this formula. To match a substring in a cell, see this formula.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
