# Sumproduct that excludes Wildcard data

#### KVASEY

##### New Member
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### shemayisroel

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP, Moderator

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)

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

#### KVASEY

##### New Member
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.

Replies
55
Views
1K
Replies
5
Views
64
Replies
11
Views
133
Replies
9
Views
134
Replies
9
Views
140