Database calculations

[MarkCBB]

Hi there,

I have a Product index table (2 Columns) 1st column are ID's 2nd column are Products. I have transposed the ID's as Columns headings to my master database using the TRANSPOSE formula as the Product Index Table changes according to campaign.

I now need to add a SUMIF column to the index table. (SUM if Index row = Master columns, then sum all records of the relevant column).

Hope this makes sense.

 

[rallcorn]

Since I'm not seeing anyone providing a more sophisticated solution, here's a simple one:

Add a sum row at bottom of your Master worksheet (I'm using row 12 for example)
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>1234</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=64>5678</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=64>91011</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>10</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">13</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">75</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>15</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">21</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">99</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>25</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">19</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">115</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>50</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">53</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">289</TD></TR></TBODY></TABLE>


On Index worksheet use Hlookup
=HLOOKUP(A2,Sheet2!$A$1:$C$12,12,FALSE)
<TABLE style="WIDTH: 176pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=235 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>ID</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=64>Product</TD><TD class=xl65 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 80pt; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent" width=107>Total from Master</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1234</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">Cookies</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">50</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>5678</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">Soap</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">53</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>91011</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">Shoes</TD><TD class=xl66 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; BORDER-BOTTOM: #ffffff; BACKGROUND-COLOR: transparent">289</TD></TR></TBODY></TABLE>

 

[MarkCBB]

Thanks for that, its worked, but I ended up changing it as I would not know where the total row is as the database is always changing.

I ended up adding a totals row a the top above the headers, and then used Index and match to find the Barcode.

Thanks.