Multiple does not equal criteria for sumif or sumproduct.

[Des1gn1ng0ne]

I have a list of products in range A1:A9 that vary in the prefix and suffix, but most of them contain "BCI" or "LAM". I can sumif each of these with the "*BCI*"/"*LAM*" syntax, but I can not get the misc products that contain neither of these criteria.
I have patched together the two formulas below that have opposite affects.

=SUMPRODUCT((A1:A9<>"*BCI*")*(A1:A9<>"*LAM*")*(B1:B9))
Returns the sum of B1:B9 in its entirety.

=SUMIF(A1:A9,OR("<>*BCI*","<>*LAM*"),B1:B9)
Returns 0; however, the "<>*BCI*" or the "<>*LAM*" alone works.

Any help would be much appreciated!!

 

[Special-K99]

Untested

To isolate BC1
=SUMPRODUCT((A1:A9="*BC1*")*(B1:B9))
To isolate LAM
=SUMPRODUCT((A1:A9="*LAM*")*(B1:B9))

To isolate BC1 OR LAM
=SUMPRODUCT(((A1:A9="*LAM*")+(A1:A9="*LAM*"))*(B1:B9))

To isolate the others
=SUM(B1:B9)-SUMPRODUCT(((A1:A9="*LAM*")+(A1:A9="*LAM*"))*(B1:B9))

 

[Colin Legg]

If cells that contain *BCI* and *LAM* are mutually exclusive - that is, no cell contains both the letters BCI and LAM - then you can use:

=SUM(B1:B9)-SUM(SUMIF(A1:A9,{"*BCI*";"*LAM*"},B1:B9))

 

[xld]

Try

=SUMPRODUCT((ISNUMBER(FIND({"BCI","LAM"},A1:A9)))*(B1:B9))

 

[Andrew Poulsom]

Another possibility to exclude BCI and LAM:

=SUMPRODUCT(--((ISERROR(SEARCH("BCI",A1:A9))+ISERROR(SEARCH("LAM",A1:A9)))=2),B1:B9)

 

[Des1gn1ng0ne]

Thank you all. I am not familiar with all of the rules of sumproduct, so this gives me many variations to tear apart and figure out.

Mr. Poulsom: Am I correct in seeing that this basically forces two errors which excludes those values from being included in the sum of B1:B9?

 

[Andrew Poulsom]

Mr. Poulsom: Am I correct in seeing that this basically forces two errors which excludes those values from being included in the sum of B1:B9?

Yes.

 

[Des1gn1ng0ne]

I have a hard enough time trying to get Excel to work "correctly" much less knowing how it will mess up to make it "work"...
Seriously, Thank you!

 

[xld]

Thank you all. I am not familiar with all of the rules of sumproduct, so this gives me many variations to tear apart and figure out.

See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation

 

[buttonmasher]

I'm trying to do exactly this, but convert it into R1C1 notation in VBA and I'm getting object-defined error.

The formula within excel does this(and works): =SUM(F8:F11)-SUM(SUMIF(C8:C11,{"IMA*"},F8:F11))

The R1C1 notation however isn't quite so nice:

ActiveCell.Offset(0, 5).FormulaR1C1 = "=SUM(R[-" & iSumTop & "]C:R[-1]C)-SUM(SUMIF(R[-" & iSumTop & "]C[-3]:R[-1]C[-3],""{""IMA*""}"",R[-" & iSumTop & "]C:R[-1]C))"

The ActiveCell.Offset is necessary for how the loop works in order to compile a table.

I've narrowed the issue down to the SUMIF formula as the SUM formula works fine if executed by itself. Any guidance would be appreciated.

Thank you.