Sumproduct that excludes Wildcard data

[KVASEY]

I'm trying to write a Sumproduct formula that will exclude some data using a wildcard. Specifically, I want to sum Column C where Column A=”OEM A” but does not include “BB” and Column B = Labor or Material.

Here'w what I tried that is not working...
=SUMPRODUCT(--(LEFT($A$1:$A$12,5)="OEM A"),--($A$1:$A$12<>"*BB*")--(LEFT($B$1:$B$12,20)="Labor")+(LEFT($B$1:$B$12,20)="Material"),$C$1:$C$12)


OEM A Model 1_______Labor______ 25
OEM A Model 1_______Material____ 25
OEM A Model 1_______Fixed Cost__25
OEM A Model 1 BB____Labor______50
OEM A Model 1 BB____Material____50
OEM A Model 1 BB____Fixed Cost__50
OEM A Model 2_______Labor______25
OEM A Model 2_______Material____ 25
OEM A Model 2_______Fixed Cost__25
OEM A BB Model 2____Labor______50
OEM A BB Model 2____Material____50
OEM A BB Model 2____Fixed Cost__50

 

[shemayisroel]

Something like this?

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 1</TD><TD>Labour</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 1</TD><TD>Material</TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 1</TD><TD>Fixed Cost</TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 1BB</TD><TD>Labour</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 1BB</TD><TD>Material</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 1BB</TD><TD>Fixed Cost</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 2</TD><TD>Labour</TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 2</TD><TD>Material</TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-FAMILY: Verdana">OEM AModel 2</TD><TD>Fixed Cost</TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-FAMILY: Verdana">OEM ABB Model 2</TD><TD>Labour</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-FAMILY: Verdana">OEM ABB Model 2</TD><TD>Material</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-FAMILY: Verdana">OEM ABB Model 2</TD><TD>Fixed Cost</TD><TD style="TEXT-ALIGN: right">50</TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=SUMPRODUCT(--(ISERROR(SEARCH("BB",A1:A12))),--(ISNUMBER(MATCH(B1:B12,{"Labour","Material"}))),(C1:C12))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[KVASEY]

This formula successfully excludes 'BB', however, I need the formula to indicate that I only want to sum "OEM A" because there will be OEM B, OEM C, etc. which need to be summed separately.

Thanks!

 

[shemayisroel]

This formula successfully excludes 'BB', however, I need the formula to indicate that I only want to sum "OEM A" because there will be OEM B, OEM C, etc. which need to be summed separately.

Thanks!

In that case, using the sample data in my previous post in D1

=SUMPRODUCT(--(ISERROR(SEARCH("BB",A1:A12))),--(ISNUMBER(SEARCH("OEM A",A1:A12))),--(ISNUMBER(MATCH(B1:B12,{"Labour","Material"}))),(C1:C12))

 

[Peter_SSs]

Does this do what you want?

=SUMPRODUCT(--(LEFT($A$1:$A$12,5)="OEM A"),--ISERROR(SEARCH("BB",$A$1:$A$12)),($B$1:$B$12="Labor")+($B$1:$B$12="Material"),$C$1:$C$12)

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(
   --(LEFT(A1:A12,3)="OEM"),
   1-ISNUMBER(SEARCH(" BB "," "&A1:A12&" ")),
   --ISNUMBER(MATCH(B1:B12,{"Labour","Material"},0)),
   C1:C12)

 

[KVASEY]

Does this do what you want?

=SUMPRODUCT(--(LEFT($A$1:$A$12,5)="OEM A"),--ISERROR(SEARCH("BB",$A$1:$A$12)),($B$1:$B$12="Labor")+($B$1:$B$12="Material"),$C$1:$C$12)

I think this one is the easiest for me to read and modify with other parameters.

Thanks for all the help, everyone.