sum all items in list based on condition

P

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have the following list of data:-

Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]750<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; text-align: right; font: 10.0px Verdana}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 10.0px Verdana}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb; padding: 0.0px 5.0px 0.0px 5.0px}</style>[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]g unsalted butter[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]lemons[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]6[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]cloves garlic[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]packs parsley[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]clementine[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]red chilli[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]packs rosemary[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]packs sage[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]24[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]bay leaves[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]500[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]g unsalted butter[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]1[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]whole nutmeg[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]24[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]smoked streaky[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]4[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]onions[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]4[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]carrots[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]4[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]celery[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]1[/TD]
[TD]<style><!--table	{mso-displayed-decimal-separator:"\.";	mso-displayed-thousand-separator:"\,";}.font5	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;}td	{padding-top:1px;	padding-right:1px;	padding-left:1px;	mso-ignore:padding;	color:windowtext;	font-size:10.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-number-format:General;	text-align:general;	vertical-align:bottom;	border:none;	mso-background-source:auto;	mso-pattern:auto;	mso-protection:locked visible;	white-space:nowrap;	mso-rotate:0;}ruby	{ruby-align:left;}rt	{color:windowtext;	font-size:8.0pt;	font-weight:400;	font-style:normal;	text-decoration:none;	font-family:Verdana;	mso-generic-font-family:auto;	mso-font-charset:0;	mso-char-type:none;	display:none;}--></style>[TABLE="width: 196"]
 <colgroup><col width="196"> </colgroup><tbody>[TR]
<!--StartFragment-->  [TD="width: 196"]bulb garlic[/TD]
<!--EndFragment--> [/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]6[/TD]
[TD]cloves garlic
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like a formula to search the column A and B and total the amount in column A next to, for example, g unsalted butter (1250). or 'cloves of garlic (12)

The current DSUM i'm using only returns the total of column A, =DSUM(A7:B22,1,B7:B22) returns 582

The current Index Match i'm using only returns the item in the column, A7:B22,MATCH("lemons",B7:B22,0),2,1) returns lemons.

Any help anyone can provide is very much appreciated.

Thanks,

Pad
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Try SUMIF - something like this


D​
E​
1​
Criteria​
Result​
2​
g unsalted butter​
1250​
3​
cloves garlic​
12​

<tbody>
</tbody>


Criteria in column D
Formula in E2 copied down
=SUMIF(B:B,D2,A:A)

Hope this helps

M.
 
Last edited:
Upvote 0
SUMIF is the simplest way (see Marcelo's post #2). If you want to stick with database functions you can define criteria. In the example below, I have named the range A6:B23 "Database" and the range E3:F4 "CR_g" (the criteria range for g unsalted butter). You can change the criteria for Item in F4 to get sums for the other items.
Excel Workbook
ABCDEF
2****CR_g*
3****QtyItem
4*****g unsalted butter
5******
6QtyItem*12501250*
7750g unsalted butter****
84lemons****
96cloves garlic****
102packs parsley****
112clementine****
122red chilli****
132packs rosemary****
142packs sage****
1524bay leaves****
16500g unsalted butter****
171whole nutmeg****
1824smoked streaky****
194onions****
204carrots****
214celery****
221bulb garlic****
236cloves garlic****
Sheet8
 
Upvote 0
Thank you for your formula, works a treat.

Is there a way you now of to eliminate the repeats quickly?

The formula copied down no gives the following

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}ruby {ruby-align:left;}rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;}--></style>
1250g unsalted butter
4lemons
6cloves garlic
2packs flat leaf parsley
2clementine
2red chilli
2packs rosemary
2packs sage
24bay leaves
1250g unsalted butter
1whole nutmeg
24smoked streaky
4onions
4carrots
4celery
1bulb garlic

<!--StartFragment--> <colgroup><col width="159" span="2"> </colgroup><tbody>
<!--EndFragment--></tbody>
Click to expand...

Is it now possible to search the two columns and delete the copy instances of 'g unsalted butter'? My data set is much larger than this one recipe.

Thank you for any help you can provide.

Pad
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
IF Condition help (I believe)
Replies
3
Views
1K
Joe4
Joe4
B
  • Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs
M
  • Question
Multiple table array
Replies
5
Views
802
Fluff
Fluff
M
  • Question
"Sort by" Formula
2
Replies
11
Views
2K
Marcelo Branco
M
M
  • Question
Transposing n rows into columns
Replies
3
Views
1K
molinal
M

Forum statistics

Threads
1,214,432
Messages
6,119,468
Members
448,900
Latest member
Fairooza

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top