Sumproduct that excludes Wildcard data

KVASEY

New Member
Joined
Apr 10, 2006
Messages
11
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

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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
Joined
Apr 10, 2006
Messages
11
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
Joined
Sep 11, 2008
Messages
1,867
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
Joined
May 28, 2005
Messages
42,327
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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
Joined
Apr 10, 2006
Messages
11
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.
 

Forum statistics

Threads
1,082,638
Messages
5,366,699
Members
400,914
Latest member
anandkb

Some videos you may like

This Week's Hot Topics

Top