Sumif - does not contain.
 

A few days ago, I asked about creating a sumif formula that adds the values
in one column for the cells in another column that contain the text string
"outlet".

I got these answers, both of which worked well.

=SUMPRODUCT(ISNUMBER(SEARCH("outlet",A2:A10))*B2:B 10)

=sumif(a:a,"*outlet*",b:b)

My question now is, how would I make this the inverse, i.e does NOT contain
€œoutlet€�?

Answer: Sumif - does not contain.
 

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:

Here's a step-by-step breakdown of the formula:

1. 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.
2. --(ISERROR(SEARCH("outlet",A2:A10))): The double negative (--) converts the TRUE value to a 1 and the FALSE value to a 0.
3. (ISERROR(SEARCH("outlet",A2:A10)))*B2:B10: This part of the formula multiplies the 1s and 0s by the values in column B.
4. 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".

Sumif - does not contain.
 

=SUMPRODUCT(--(ISERROR(SEARCH("outlet",A2:A10))),B2:B10)


=SUMIF(A:A,"<*outlet*",B:B)


--


Regards,


Peo Sjoblom




"jerrymcm" wrote in message
...
A few days ago, I asked about creating a sumif formula that adds the values
in one column for the cells in another column that contain the text string
"outlet".

I got these answers, both of which worked well.

=SUMPRODUCT(ISNUMBER(SEARCH("outlet",A2:A10))*B2:B 10)

=sumif(a:a,"*outlet*",b:b)

My question now is, how would I make this the inverse, i.e does NOT
contain
"outlet"?

Sumif - does not contain.
 

Just replace ISNUMBER with ISERROR in the sumproduct
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jerrymcm" wrote:
A few days ago, I asked about creating a sumif formula that adds the values
in one column for the cells in another column that contain the text string
"outlet".

I got these answers, both of which worked well.

=SUMPRODUCT(ISNUMBER(SEARCH("outlet",A2:A10))*B2:B 10)

=sumif(a:a,"*outlet*",b:b)

My question now is, how would I make this the inverse, i.e does NOT contain
€œoutlet€�?