To create a SUMIF formula that adds the values in one column for the cells in another column that does NOT contain the text string "outlet", you can use the following formula:
Code:
=SUMPRODUCT(--(ISERROR(SEARCH("outlet",A2:A10)))*B2:B10)
Here's a step-by-step breakdown of the formula:- ISERROR(SEARCH("outlet",A2:A10)): This part of the formula searches for the text string "outlet" in cells A2 to A10 and returns an error if it is not found. The ISERROR function then converts the error to a TRUE value.
- --(ISERROR(SEARCH("outlet",A2:A10))): The double negative (--) converts the TRUE value to a 1 and the FALSE value to a 0.
- (ISERROR(SEARCH("outlet",A2:A10)))*B2:B10: This part of the formula multiplies the 1s and 0s by the values in column B.
- SUMPRODUCT((ISERROR(SEARCH("outlet",A2:A10)))*B2:B 10): The SUMPRODUCT function adds up the values in the resulting array.
This formula should give you the sum of values in column B for the cells in column A that do NOT contain the text string "outlet".