How to construct an excel sumif formula with multiple criteria using keywords that does not match exactly.

rushana ceasar · Dec 8, 2011

<TABLE style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 100%; BACKGROUND: white; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid; mso-cellspacing: 0cm; mso-border-alt: solid #3867A6 .75pt; mso-yfti-tbllook: 1184; mso-padding-alt: 4.5pt 4.5pt 4.5pt 4.5pt" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #3867a6; BORDER-LEFT: #3867a6; PADDING-BOTTOM: 4.5pt; PADDING-LEFT: 4.5pt; PADDING-RIGHT: 4.5pt; BACKGROUND: #efefef; BORDER-TOP: #3867a6; BORDER-RIGHT: white 1pt solid; PADDING-TOP: 4.5pt; mso-border-right-alt: solid white .75pt" vAlign=top>How to construct an excel sumif formula with multiple criteria using keywords that does not match exactly. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>

Hi

I have a database that I have to summarise by nature (ie major or minor asset) and then by class of assets (ie machinery, software etc).

However, the only unique identifier is the description of the asset and this is not always the same. It does however contain keywords such as software.

The problem is that software is not always the only word in the cell. It would read for Example:
- Software license
- Software 2012
- Computer Software
- Installation of software

How do I construct a formula to sum for example all minor assets and for all items containing the word "software"? <o

></o

>
</TD></TR><TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: white 1pt solid; BORDER-LEFT: white 1pt solid; PADDING-BOTTOM: 4.5pt; PADDING-LEFT: 4.5pt; PADDING-RIGHT: 4.5pt; BACKGROUND: #e6e6e6; BORDER-TOP: white 1pt solid; BORDER-RIGHT: white 1pt solid; PADDING-TOP: 4.5pt; mso-border-alt: solid white .75pt; mso-border-top-alt: solid white .25pt"><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o

referrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v

ath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v

ath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 11.25pt; HEIGHT: 11.25pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_4 alt="Description: rushana ceasar is offline" type="#_x0000_t75" o:spid="_x0000_i1030"><v:imagedata src="file:///C:\Users\55475990\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif" o:title="rushana ceasar is offline"></v:imagedata></v:shape> <o

></o

>
</TD><TD style="BORDER-BOTTOM: white 1pt solid; BORDER-LEFT: white 1pt solid; PADDING-BOTTOM: 4.5pt; PADDING-LEFT: 4.5pt; PADDING-RIGHT: 4.5pt; BACKGROUND: #efefef; BORDER-TOP: white 1pt solid; BORDER-RIGHT: white 1pt solid; PADDING-TOP: 4.5pt; mso-border-right-alt: .75pt; mso-border-top-alt: .25pt; mso-border-left-alt: .25pt; mso-border-bottom-alt: .75pt; mso-border-color-alt: white; mso-border-style-alt: solid">

<v:shape style="WIDTH: 52.5pt; HEIGHT: 16.5pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_3 href="http://www.mrexcel.com/forum/newreply.php?do=newreply&p=2957547" alt="Description: Reply With Quote" type="#_x0000_t75" o:spid="_x0000_i1029" o:button="t"><v:imagedata src="file:///C:\Users\55475990\AppData\Local\Temp\msohtmlclip1\01\clip_image002.gif" o:title="Reply With Quote"></v:imagedata></v:shape><o

></o

>

</TD></TR></TBODY></TABLE>

<o

> </o

>

<TABLE style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; MARGIN: auto auto auto 1.55pt; BACKGROUND: white; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid; mso-cellspacing: 0cm; mso-border-alt: solid #3867A6 .75pt; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 0cm 0cm 0cm" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: white 1pt solid; BORDER-LEFT: white 1pt solid; PADDING-BOTTOM: 4.5pt; PADDING-LEFT: 4.5pt; WIDTH: 73.05pt; PADDING-RIGHT: 4.5pt; BACKGROUND: #e6e6e6; BORDER-TOP: white 1pt solid; BORDER-RIGHT: white 1pt solid; PADDING-TOP: 4.5pt; mso-border-right-alt: .75pt; mso-border-top-alt: .25pt; mso-border-left-alt: .75pt; mso-border-bottom-alt: .25pt; mso-border-color-alt: white; mso-border-style-alt: solid" vAlign=top width=97>Sal Paradise******** type=text/javascript> vbmenu_register("postmenu_2957585", true); *********> <o

></o

>
Board Regular<o

></o

>
<o

></o

>
Join Date: Oct 2006<o

></o

>
Posts: 2,031 <o

></o

>
</TD><TD style="BORDER-BOTTOM: #3867a6; BORDER-LEFT: #3867a6; PADDING-BOTTOM: 4.5pt; PADDING-LEFT: 4.5pt; PADDING-RIGHT: 4.5pt; BACKGROUND: #efefef; BORDER-TOP: #3867a6; BORDER-RIGHT: white 1pt solid; PADDING-TOP: 4.5pt; mso-border-right-alt: solid white .75pt" vAlign=top><v:shape style="WIDTH: 12pt; HEIGHT: 12pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_1 alt="Description: Default" type="#_x0000_t75" o:spid="_x0000_i1028"><v:imagedata src="file:///C:\Users\55475990\AppData\Local\Temp\msohtmlclip1\01\clip_image003.gif" o:title="Default"></v:imagedata></v:shape>Re: How to construct an excel sumif formula with multiple criteria using keywords that does not match exactly. <o

></o

>

Excel 2003 <o

></o

>
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; BORDER-COLLAPSE: collapse; BACKGROUND: white; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid #A6AAB6 .75pt; mso-yfti-tbllook: 1184; mso-padding-alt: 1.5pt 1.5pt 1.5pt 1.5pt" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0><THEAD><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #a6aab6 1pt solid; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt; mso-border-top-alt: solid #A6AAB6 .75pt; mso-border-left-alt: solid #A6AAB6 .75pt"></TD><TD style="BORDER-BOTTOM: #ffffff; BORDER-LEFT: #ffffff; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #e0e0f0; BORDER-TOP: #a6aab6 1pt solid; BORDER-RIGHT: #ffffff; PADDING-TOP: 1.5pt; mso-border-top-alt: solid #A6AAB6 .75pt">

A<o></o>