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!!

 

[Andrew Poulsom]

What is ActiveCell and iSumTop? The macro recorder will give you the syntax for the formula in R1C1 notation. I notice that you have unnecessary double quotes around the curly braces.

 

[buttonmasher]

What is ActiveCell and iSumTop? The macro recorder will give you the syntax for the formula in R1C1 notation. I notice that you have unnecessary double quotes around the curly braces.

ActiveCell is the cell that the formula is being recorded in. For the purposes of my original formula, it would be cell F12. iSumTop is the top row of the values being summed up. This number is never the same every time the loop runs through.

 

[Andrew Poulsom]

Apart from the double quotes issue I mentioned previously, you are using a relative row reference when you probably need it to be absolute. Does this work for you?

    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))"

 

[buttonmasher]

Turns out it was the double quotes that was the problem. Had to leave the formula the way it was since I was working with finite instances but building multiple tables with the same loop.

Thanks!