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
 
Sort by date Sort by votes
Try SUMIF - something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Criteria​
[/TD]
[TD]
Result​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
g unsalted butter​
[/TD]
[TD]
1250​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
cloves garlic​
[/TD]
[TD]
12​
[/TD]
[/TR]
</tbody>[/TABLE]


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>[TABLE="width: 318"]
<!--StartFragment--> <colgroup><col width="159" span="2"> </colgroup><tbody>[TR]
[TD="width: 159, align: right"]1250[/TD]
[TD="width: 159"]g unsalted butter[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]lemons[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]cloves garlic[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]packs flat leaf parsley[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]clementine[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]red chilli[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]packs rosemary[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]packs sage[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]bay leaves[/TD]
[/TR]
[TR]
[TD="align: right"]1250[/TD]
[TD]g unsalted butter[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]whole nutmeg[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]smoked streaky[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]onions[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]carrots[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]celery[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]bulb garlic[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]
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

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
904
smozgur
smozgur
C
  • Question Question
IF Condition help (I believe)
Replies
3
Views
1K
Joe4
Joe4
B
  • Question Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs
M
  • Question Question
Multiple table array
Replies
5
Views
955
Fluff
Fluff
M
  • Question Question
"Sort by" Formula
2
Replies
11
Views
2K
Marcelo Branco
M

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